关于mysql，sqlserverl，与oracle数据库连接总结

首先准备工具类，其中需要修改的地方分别做标注

代码一sqlserver为例，不同数据库只需修改我所标记的第一处和第二处

mysql 第一处应为：com.mysql.jdbc.Driver 第二处url 为：jdbc:mysql://127.0.0.1:3306/javaweb //3306位端口，javaweb为数据库名

Oracle 第一处应为：oracle.jdbc.OracleDriver 第二处url 为：jdbc:oracle:thin:@localhost:1522:orcl //1522为端口，orcl为数据库名

sql语句因数据库不同略有差异，但都大同小异。

package Util;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class DBUtil {

    public  static  Connection getConnection(){

        try {

            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();   //1.此处根据数据库修改

        } catch (InstantiationException | IllegalAccessException | ClassNotFoundException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

        String user = "sa";  　　　　　　 //用户名

        String password = "123456";   //密码

        String url = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=keji";  //2.数据库名

        Connection connection = null;

        try {

            //2 �������Ӷ���connection

             connection = DriverManager.getConnection(url,user,password);

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

        return connection;

    }

    public static void close(Connection connection ) {

        try {

            if (connection != null) {

                connection.close();

            }

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

    }

    public static void close(PreparedStatement preparedStatement ) {

        try {

            if (preparedStatement != null) {

                preparedStatement.close();

            }

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

    }

    public static void close(ResultSet resultSet ) {

        try {

            if (resultSet != null) {

                resultSet.close();

            }

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

    }

}

对数据库操作代码

简单的插入

public void add (User user){

        Connection connection=DBUtil.getConnection();

        String sql = "select count(*) from t_user where username = ?";

        PreparedStatement preparedStatement = null;

        ResultSet resultSet = null;

        try {

            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setString(1, user.getUsername());

            resultSet = preparedStatement.executeQuery();

            while(resultSet.next()) {

                if (resultSet.getInt(1) > 0) {

                    throw new UserException("用户名存在") ;

                }

            }

            sql = "insert into t_user(username,password) values (?,?)";

            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setString(1, user.getUsername());

            preparedStatement.setString(2, user.getPassword());

            preparedStatement.executeUpdate();

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

        finally{

            DBUtil.close(resultSet);

            DBUtil.close(preparedStatement);

            DBUtil.close(connection);

        }

    }

删除

    public void delete(String username) {

        // TODO Auto-generated method stub

        Connection connection = DBUtil.getConnection();

        String sql = "delete from t_user where username = ?";

        PreparedStatement preparedStatement = null;

        try {

            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setString(1, username);

            preparedStatement.executeUpdate();

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }finally {

            DBUtil.close(preparedStatement);

            DBUtil.close(connection);

        }

    }

修改

    public void update(User user) {

        // TODO Auto-generated method stub

        Connection connection = DBUtil.getConnection();

        //׼��sql���

        String sql = "update t_user set password = ? where username = ?";

        //������䴫�����

        PreparedStatement preparedStatement = null;

        try {

            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setString(1, user.getPassword());

            preparedStatement.setString(2, user.getUsername());

            preparedStatement.executeUpdate();

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }finally {

            DBUtil.close(preparedStatement);

            DBUtil.close(connection);

        }

    }

查询

    public User load(String username) {

        // TODO Auto-generated method stub

        Connection connection = DBUtil.getConnection();

        //׼��sql���

        String sql = "select * from t_user  where username = ?";

        //������䴫�����

        PreparedStatement preparedStatement = null;

        ResultSet resultSet = null;

        User user = null;

        try {

            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setString(1, username);

            resultSet = preparedStatement.executeQuery();

            while(resultSet.next()) {

                user = new User();

                user.setId(resultSet.getInt("id"));

                user.setUsername(username);

                user.setBalance(resultSet.getFloat("balance"));

                user.setPassword(resultSet.getString("password"));

                user.setType(resultSet.getInt("type"));

                user.setCredit(resultSet.getFloat("credit"));

            }

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }finally {

            DBUtil.close(resultSet);

            DBUtil.close(preparedStatement);

            DBUtil.close(connection);

        }

        return user;

    }

巴特西

关于mysql，sqlserverl，与oracle数据库连接总结

最新文章

热门文章