JDBC一个简单的数据库操作流程

简单操作的例子

public class demo {
    public static void main(String[] args) {
        try {
            //1、加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/demo?useSSL=false";
            String username = "root";
            String password = "123456";
            //2、获得连接
            Connection connection = DriverManager.getConnection(url, username, password);
            //3、准备sql语句
            String sql = "insert into user_info (username, password, email, is_admin) values ('adc', '123', 'abc@csu.edu.cn',0)";
            //4、执行sql语句 有两个执行对象 Statement PreparedStatement
            Statement statement = connection.createStatement();
            //5、获得结果
            int result = statement.executeUpdate(sql);
            //6、判断结果是否为成功
            if(result == 1){
                System.out.println("执行成功！");
            }
            //7、关闭获取过的对象
            statement.close();
            connection.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

所操作的表

---

具体的流程

流程说明：设计一个JDBC对数据库进行增删改查操作（包涵POJO、DBUil、DAO）

---

POJO层存放对象

package jdbc;
//POJO
public class User {
    private int id;
    private String username;
    private String password;
    private String email;
    private boolean admin;

    public User(){

    }

    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;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public boolean isAdmin() {
        return admin;
    }

    public void setAdmin(boolean admin) {
        this.admin = admin;
    }
}

---

DBUtil层存放公共代码，封装数据库连接操作

package jdbc;

import java.sql.*;
//DBUtil封装对数据库连接操作
//工具类，jdbc中专门用来写公共代码，就比如说连接数据库
public class DBUtil {
    //写成静态的，其他的方法不能进行修改
    //驱动的位置
    private static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";
    //数据库连接URL，不同版本的mysql这里可能有所不同, 其中的demo是你要连接的数据库的名字
    private static final String URL = "jdbc:mysql://127.0.0.1:3306/demo?useSSL=false";
    //这里的账户和密码，因人而异
    private static final String USERNAME = "root";
    private static final String PASSWORD =  "123456";

    //获取数据库连接
    public static Connection getConnection() throws Exception{
        //加载驱动
        Class.forName(DRIVER_CLASS);
        //获取数据库连接
        Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        //返回连接
        return connection;
    }
    //关闭数据库连接
    public static void closeConnection(Connection connection) throws Exception{
        if(connection != null){
            connection.close();
        }
    }
    //关闭执行对象Statement
    public static void closeStatement(Statement statement) throws Exception{
        if(statement != null){
            statement.close();
        }
    }
    //关闭执行对象PreparedStatement
    public static void closePreparedStatement(PreparedStatement preparedStatement) throws Exception{
        if(preparedStatement != null){
            preparedStatement.close();
        }
    }
    //关闭结果集
    public static void closeResultSet(ResultSet resultSet) throws Exception{
        if(resultSet != null){
            resultSet.close();
        }
    }

      //测试一下
//    public static void main(String[] args) {
//        try{
//            Connection connection = getConnection();
//            closeConnection(connection);
//        }catch (Exception e){
//            e.printStackTrace();
//        }
//    }
}

---

DAO层封装对表的增删改查功能

package jdbc;

import com.sun.jdi.event.ExceptionEvent;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

//DAO封装对表的操作，用于对User数据表进行增删改查
public class UserDAO {
    //写成静态方便修改  sql语句操作关键字一般大写，表名小写
    private static final String INSERT_USER = "INSERT INTO user_info (username, password, email, is_admin) VALUES (?,?,?,?)";
    //根据id删除
    private static final String DELETE_USER = "DELETE FROM user_info WHERE id = ?";
    //根据id修改数据
    private static final String UPDATE_USER = "UPDATE user_info SET username = ?,password = ?, email = ?,is_admin = ? WHERE id = ?";
    //查询表中所有数据
    private static final String SELECT_ALL = "SELECT * FROM user_info";

    //新增一个用户
    public int insertUser(User user){
        //设置int返回值，用于判断操作是否成功
        int result = 0;
        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USER);
            //这里的1，2，3，4所放的东西的顺序分别是问号的所表示东西的顺序
            preparedStatement.setString(1, user.getUsername());
            preparedStatement.setString(2, user.getPassword());
            preparedStatement.setString(3, user.getEmail());
            preparedStatement.setInt(4, user.isAdmin()?0 : 1);
            result = preparedStatement.executeUpdate();

            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    //根据id删除用户
    public int deleteUser(User user){
        //设置int返回值，用于判断操作是否成功
        int result = 0;
        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(DELETE_USER);
            preparedStatement.setInt(1, user.getId());
            result = preparedStatement.executeUpdate();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    //根据id更新用户信息
    public int updateUser(User user){
        //设置int返回值，用于判断操作是否成功
        int result = 0;
        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_USER);
            //这里的1，2，3，4所放的东西的顺序分别是问号的所表示东西的顺序
            preparedStatement.setString(1, user.getUsername());
            preparedStatement.setString(2, user.getPassword());
            preparedStatement.setString(3, user.getEmail());
            preparedStatement.setInt(4, user.isAdmin()?0 : 1);
            preparedStatement.setInt(5,user.getId());
            result = preparedStatement.executeUpdate();

            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }
    //查询表中所有数据
    public List<User> selectAll(){
        List<User> userList = new ArrayList<>();
        try{
            Connection connection = DBUtil.getConnection();
            //查询所有数据不需要参数数 不需要使用PreparedStatement操作对象 使用Statement即可
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(SELECT_ALL);
            while(resultSet.next()){
                User user = new User();
                user.setId(resultSet.getInt(1));
                user.setUsername(resultSet.getString(2));
                user.setPassword(resultSet.getString(3));
                user.setEmail(resultSet.getString(4));
                user.setAdmin(resultSet.getInt(5) == 0);
                userList.add(user);
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closeStatement(statement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
        return userList;
    }

    public static void main(String[] args){
        //测试insertUser()方法是否成功
//        User user = new User();
//        user.setUsername("aaa");
//        user.setPassword("123");
//        user.setEmail("aaa@csu.edu.cn");
//        user.setAdmin(false);
//
//        int result = new UserDAO().insertUser(user);
//        System.out.println(result);//返回1就说明添加成功啦
        //测试selectAll()是否成功
        List<User> userList = new UserDAO().selectAll();
        for(User user : userList){
            System.out.println(user.getId() + "," + user.getUsername() + "," + user.getPassword() + "," + user.getEmail() + "," + user.isAdmin());
        }
    }
}