Mysql 数据库连接、访问、增删查改

Mysql数据库常用操作

Mysql建库、建表

CREATE DATABASE spring_jdbc_test;

DROP TABLE IF EXISTS `question_validate_log`;
CREATE TABLE IF NOT EXISTS `student`(
    `id` int unsigned auto_increment,
    `name` VARCHAR(50) NOT NULL,
    `sex` ENUM("male","female"),
    `age` tinyint unsigned,
    PRIMARY KEY (id)
    )ENGINE=innodb DEFAULT charset = utf8;

CRUD

INSERT INTO student ( `name`, `sex`, `age` ) VALUES ( 'tom', 1, 22 )；
select * from student where id =1;
delete from student where id = 5;
update student set name = "jerry" where id =2 ;

事物

一个连接的事物在处理过程中时
另外一个连接客户端在事物提交之前 ，并不会知道这个事物的执行结果，否则就脏读了。

JDBC数据库连接并且进行CRUD、设置简单的事务

CRUD接口

public interface DbCRUD {

    int create(Student student);

    Student queryById(int id);

    boolean delete(int id);

    boolean update(Student student);
}

CRUD实现

    @Override
    public int create(Student student) {
        int res = -1;
        try {
            String insertCommand = "INSERT INTO " + TABLE_NAME + " ( " + ALL_COLUMN_WITHOUT_ID + " )" +
                    " VALUES " +
                    "( ?, ?, ? )";
            PreparedStatement statement = conn.prepareStatement(insertCommand, Statement.RETURN_GENERATED_KEYS);
            statement.setString(1, student.getName());
            statement.setInt(2, student.getSex().toInt());
            statement.setInt(3, student.getAge());
            res = statement.executeUpdate();
            ResultSet resultSet = statement.getGeneratedKeys();
            while (resultSet.next()) {
                res = resultSet.getInt(1);
            }
            try {
                resultSet.close();
            } catch (Exception e) {
                System.out.println("resultSet close exception");
            }
            try {
                statement.close();
            } catch (Exception e) {
                System.out.println("statement close exception");
            }
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }
        return res;
    }

    @Override
    public Student queryById(int id) {
        Student student = null;
        try {
            String queryCommand = "SELECT " + ALL_COLUMN + " FROM " + TABLE_NAME + " WHERE id = ?";
            PreparedStatement statement = conn.prepareStatement(queryCommand);
            statement.setInt(1, id);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                student = new Student();
                student.setId(resultSet.getInt(1));
                student.setName(resultSet.getString(2));
                student.setSex(Sex.findByString(resultSet.getString(3)));
                student.setAge(resultSet.getInt(4));
            }
            try {
                resultSet.close();
            } catch (Exception e) {
                System.out.println("resultSet close exception");
            }
            try {
                statement.close();
            } catch (Exception e) {
                System.out.println("statement close exception");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return student;
    }

    @Override
    public boolean delete(int id) {
        boolean res = false;
        String deleteCommand = "DELETE FROM " + TABLE_NAME + " WHERE id = ?";
        try {
            PreparedStatement statement = conn.prepareStatement(deleteCommand);
            statement.setInt(1, id);
            res = statement.executeUpdate() > 0;
            try {
                statement.close();
            } catch (Exception e) {
                System.out.println("statement close exception");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return res;
    }

    @Override
    public boolean update(Student student) {
        //需要判断id的正确性、student内容的正确性
        boolean res = false;
        if (student.getId() > 0) {
            StringBuilder updateCommand = new StringBuilder();
            updateCommand.append("UPDATE " + TABLE_NAME + " SET ");
            List<String> args = new ArrayList<>();
            if (StringUtils.isNotBlank(student.getName())) {
                args.add("name = ?");
            }
            if (student.getSex() != null) {
                args.add("sex = ?");
            }
            if (student.getAge() > 0) {
                args.add("age = ?");
            }
            updateCommand.append(StringUtils.join(args, ", "));
            updateCommand.append(" WHERE id = ?");
            try {
                PreparedStatement statement = conn.prepareStatement(updateCommand.toString());
                if (StringUtils.isNotBlank(student.getName())) {
                    statement.setString(1, student.getName());
                }
                if (student.getSex() != null) {
                    statement.setInt(2, student.getSex().toInt());
                }
                if (student.getAge() > 0) {
                    statement.setInt(3, student.getAge());
                }
                statement.setInt(4, student.getId());
                res = statement.executeUpdate() > 0;
                try {
                    statement.close();
                } catch (Exception e) {
                    System.out.println("statement close exception");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return res;
    }

    //mysql驱动包名
    private static final String DRIVER_NAME = "com.mysql.cj.jdbc.Driver";
    //数据库连接地址
    private static final String URL = "jdbc:mysql://127.0.0.1:3306/spring_jdbc_test";
    //用户名
    private static final String USER_NAME = "root";
    //密码
    private static final String PASSWORD = "***";

    @Test
    private void testDb() {
        Dbtest dbtest = new Dbtest();
        try {
            //加载mysql的驱动类
            Class.forName(DRIVER_NAME);
            //获取数据库连接
            dbtest.conn = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
            //设置事物
            dbtest.conn.setAutoCommit(false);
            dbtest.create(new Student(-1, "tom", Sex.MALE, 22));
            System.out.println(dbtest.queryById(5));
            dbtest.conn.commit();
            //设置回默认值
            dbtest.conn.setAutoCommit(false);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dbtest.conn != null) {
                try {
                    dbtest.conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

JDBC语句对象：

常规语句General Statement

预置语句Prepared Statement

可调用语句Callable Statement