JDBC数据库连接与常用操作(增删改查)

一、JDBC

JDBC的全称为JAVA DATABASE CONNECTION(java 数据库连接)
要使用JDBC，必须导入相应的jar包，如使用ms sqlserver数据库需要导入对应的jar包，如mssql-jdbc-7.2.2.jre8.jar，使用mysql数据库需要导入对应的jar包，如mysql-connector-java-8.0.16.jar

二、JDBC数据库连接步骤

加载驱动

Class.forName("com.mysql.jdbc.Driver");

创建连接

//mysql8.0.16版本jdbc连接的url,db_test为要连上的数据库名
String url = "jdbc:mysql://127.0.0.1:3306/db_test?useSSL=true&serverTimezone=UTC&characterEncoding=utf-8"; 
//旧版本数据库连接的url
String url = "jdbc:mysql://127.0.0.1:3306/db_test"
String username = "root";
String password = "123456";
connection = DriverManager.getConnection(url, username, password);

写SQL语句

String insertUser = "insert into tb_userinfo(username, password) values(?, ?)"; //插入用户
String deleteUser = "delete from tb_userinfo where id = ?"; //删除用户
String updateUser = "update tb_userinfo set username = ?, password = ? where id = ?"; //更改用户信息
String selectAllUser = "select * from tb_userinfo"; //查询所有用户

根据SQL语句得到statement对象

//增加用户
statement = connection.prepareStatement(insertUser);
statement.setString(1, "kate");
statement.setString(2, "123");

//删除用户
statement = connection.prepareStatement(deleteUser);
statement.setInt(1, 1);

//修改用户
String updateUser = "update tb_userinfo set username = ?, password = ? where id = ?";
statement = connection.prepareStatement(updateUser);
statement.setString(1, "zhangsan");
statement.setString(2, "zhangsan123");
statement.setInt(3, 2);

//查询用户
statement = connection.prepareStatement(selectAllUser);

执行SQL语句,得到结果集

//增加用户、删除用户、修改用户
resultSet = statement.executeUpdate();
//查询用户
resultSet = statement.executeQuery();

处理结果集

//增/删/改
if (resultSet == 0) {
    System.out.println("插入数据失败!");
}
//查询
while(resultSet.next()) {

    int id = resultSet.getInt("id");
    String username = resultSet.getString("username");
    String password = resultSet.getString("password");
    System.out.println("User:[id:" + id + ",username:" + username +
                       ",password:" + password + "]");
}

释放数据库连接对象

//释放结果集对象
if(resultSet != null) {
    try {
        resultSet.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}
//释放数据库操作对象
if(statement != null) {
    try {
        statement.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}
//释放数据库连接对象
if(connection != null) {
    try {
        connection.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

三、数据库访问的分层实现(MVC模式)

Model层

与数据库表中数据相对应，一个表对应一个实体类

实体类User

public class User {

    private int id;
    private String username;
    private String password;


    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

工具类DBUtil

主要完成数据库的连接与资源的释放

public class DBUtils {

    /** * 1.获取数据库连接对象 * @return 数据库连接对象 */
    public static Connection getConnection() {

        Connection connection = null;
        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.创建连接
            String url = "jdbc:mysql://127.0.0.1:3306/db_test?useSSL=true&serverTimezone=UTC&characterEncoding=utf-8";
            String username = "root";
            String password = "123456";
            connection = DriverManager.getConnection(url, username, password);

            // BASE DAO
            return connection;
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /** * 2.关闭连接,释放资源 * @param connection 数据库连接对象 * @param statement 数据库操作对象 * @param resultSet 结果集对象 */
    public static void free(Connection connection, Statement statement, ResultSet resultSet) {

        //释放结果集对象
        if(resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        //释放数据库操作对象
        if(statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        //释放数据库连接对象
        if(connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

视图层view
主要进行用户界面操作。
loginUI类

public class LoginUI {

    public static void main(String[] args) {

        Scanner scanner = new Scanner(System.in);
        System.out.println("please input your username:");
        String username = scanner.next();
        System.out.println("please input your password:");
        String password = scanner.next();
        User loginUser = new User(username, password);
        LoginService loginService = new LoginServiceImpl();
        if(loginService.checkLoginAccess(loginUser)) {
            System.out.println("登录成功!");
        } else {
            System.out.println("登录失败~");
        }
    }

}

dao层
与model层相对应，执行与数据库访问的相关操作。
LoginDao接口

public interface LoginDao {

    boolean ckeckLoginAccess(User loginUser);

}

LoginDaoImpl 实现loginDao接口

public class LoginDaoImpl implements LoginDao {

    @Override
    public boolean ckeckLoginAccess(User loginUser) {

        Connection connection = DBUtils.getConnection();
        if(connection != null) {
            String sql = "select count(*) as num from tb_userinfo where username = ? and password = ?";
            try {
                PreparedStatement pstm = connection.prepareStatement(sql);
                pstm.setString(1, loginUser.getUsername());
                pstm.setString(2, loginUser.getPassword());
                ResultSet resultSet = pstm.executeQuery();
                if(resultSet.next()) {
                    int num = resultSet.getInt("num");
                    if(num != 0) {
                        return true;
                    }
                }

                return false;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

}

service层
调用dao层，完成相应的逻辑功能。
LoginService接口:

public interface LoginService {

    boolean checkLoginAccess(User loginUser);
}

LoginServiceImpl类实现LoginService接口：

public class LoginServiceImpl implements LoginService {

    @Override
    public boolean checkLoginAccess(User loginUser) {

        return new LoginDaoImpl().ckeckLoginAccess(loginUser);
    }

}