JDBC

Posted on 2021-02-23 In 计算机 , java Views:

JDBC使得我们可以通过java代码访问数据库。

1 将mysql-connector jar包载入到当前项目中

2 使用java代码连接到数据库

String name = "com.mysql.cj.jdbc.Driver";
PreparedStatement psta = null;
Connection con = null;
        
try {
    /*1. 加载jdbc驱动*/
    Class c = Class.forName(name);

    /*2. 与mysql建立连接*/
    con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test",
                                      "root",
                                      ""); //get connection

    /*3. 创建会话并预处理sql语句（待传入的参数用占位符取代？）*/
    String sql = "update mydata" + " set username = ?" + " where username = ?";
    psta = con.prepareStatement("");

    /*4. 设置占位符的值*/
    psta.setString(1,"changed");//第一个参数代表第几个占位符
    psta.setString(2,"4");

    /*5. 执行sql语句，sql语句分为两类：*/
    ResultSet rs = psta.executeQuery();//执行查询表的sql，返回值为查询结果表，用Resultset接收。
    int re = psta.executeUpdate();//执行修改表的sql，返回值为受影响的行数，用int接收。本例为update因此应该用这句接收。

    System.out.println(re);


} catch (ClassNotFoundException e) {
    e.printStackTrace();
} catch (SQLException throwables) {
    throwables.printStackTrace();
} finally { //最后别忘了关闭资源通道
    if(psta!=null) {
        try {
            psta.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    if(con!=null){
        try {
            con.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

预编译的好处是提高代码的可读性、安全性，并且因为sql代码是预编译的，所以性能得到了提升。

3 修改、删除和插入

要对表进行操作，首先要知道表的结构，比如查询mydata表，先查看它的结构

对表进行修改，要用executeUpdate()执行sql语句。

【插入】

PreparedStatement psta = con.prepareStatement("insert into mydata values(?,?,?,?)"); 
psta.setInt(1,id);
psta.setString(2,username);
psta.setInt(3,password); //本例password为int类型
psta.setInt(4,score);

int r = psta.executeUpdate();//执行sql语句

if(r>0){
    System.out.println("sql success!");
} else{
    System.out.println("sql failed");
}

【修改】

PreparedStatement psta = con.prepareStatement("update mydata set username='newName' where username=?");
psta.setString(1,username);

int r = psta.executeUpdate();//执行sql语句

if(r>0){
    System.out.println("sql success!");
} else{
    System.out.println("sql failed");
}

【删除】

PreparedStatement psta = con.prepareStatement("delete from data where username=?"); 
psta.setString(1,username);

int r = psta.executeUpdate();//执行sql语句

if(r>0){
    System.out.println("sql success!");
} else{
    System.out.println("sql failed");
}

4 查询

要做查询，首先要知道表的结构，比如查询mydata表，先查看它的结构

对表进行查询，要用executeQuery()执行sql语句。

然后写查询语句，因为查询的结果一般有多条，所以用ResultSet接收查询结果

1	ResultSet rs = psta.executeQuery();

注意这里调用的方法是executeQuery，而不是executeUpdate。

用变量接收查询结果

while(rs.next()){
    int id = rs.getInt(1); //方法中的参数代表第几列，也可以直接传列名（字符串形式）
    String username = rs.getString(2);
    int password = rs.getInt(3);
    int score = rs.getInt(4);
}

5 事务控制

假如要执行很多条sql命令，多数情况下，如果其中某一条命令执行失败，那么其他命令的执行就没有意义了，这时想让数据表恢复到所有这一批sql命令执行之前的状态，就要通过事务来完成。将一批sql命令定义为一个事务，事务中只要有sql语句执行失败，数据库就会回滚到事务开始时的状态，只有事务中的sql命令全部执行成功后才会将事务（即其中的所有sql命令）全部提交。

其原理就是在事务开始前，存储引擎先对事务涉及到的表内容做一个记录备份，如果需要回滚，就将备份覆盖到受影响的字段；不需要回滚，就将备份删除。

5.1 修改mysql默认引擎

通过show engines; 查看当前mysql服务器支持的引擎

可以看到默认引擎是MyISAM，它不支持事务操作（也不支持外键）。所以想要进行事务操作，要先将默认引擎改为InnoDB（支持事务，且默认将每一条sql语句视为一个完整事务，执行con.setAutoCommit(false);命令可改为手动提交事务）。

innodb速度没有myisam快

在my.ini文件中，修改default-storage-engine=MYISAM 即可更换默认引擎（修改完后记得重启mysql服务器）

因为之前的表是在myisam引擎下创建的，因此要对旧表的引擎做一个更新（更换引擎后新创建的表不用），执行：alter table mydata engine=innodb;

5.2 通过JDBC提交事务

获取到连接con后

/**
* setAutoCommit(): Sets this connection's auto-commit mode to the 
* given state. If a connection is in auto-commit mode, then all its SQL 
* statements will be executed and committed as individual 
* transactions. Otherwise, its SQL statements are grouped into 
* transactions that are terminated by a call to either the method
* commit or the method rollback. By default, new connections are 
* in auto-commit mode.
*/
con.setAutoCommit(false); //申请手动提交事务
PreparedStatement ps = con.prepareStatement;
try{
    //第一句
    psta = con.prepareStatement("delete from mydata where username=?");
    psta.setString(1,"tom");
    psta.executeUpdate(); 
    
    //第二句
    psta = con.prepareStatement("delete from mydatda where username=?"); //这句故意写错
    psta.setString(1,"lisa");
    psta.executeUpdate(); //执行到这会捕捉到SQLException，跳到catch块
    
    //上面捕捉到异常了，这句不会执行
    con.commit(); 

} catch (SQLException e){
    e.printStackTrace();
    con.rollback(); //回滚，将表恢复到try开始前的状态
}

6 DAO封装

Database Access Object(DAO)封装了针对某一张数据表的操作（增删改查），这样之后要对某张表进行操作，直接通过DAO进行即可，为开发提供了方便。

【规范】

DAO类名称：表名+Dao。比如Emp表的DAO叫EmpDao

DAO所属包名称：公司域名.dao，比如com.google.dao

【DAO类示例——user_info表的dao】

public class User_infoDao {
    
    public int add(String username, String password){
        int re = 0;
        String sql = "insert into user_info (username, password, from_date) values (?,?,?)";
        java.sql.Date date = new java.sql.Date(System.currentTimeMillis());
        try {
            PreparedStatement ps = MyTool.createPreStatement(sql);
            ps.setString(1,username);
            ps.setString(2,password);
            ps.setDate(3,date);
            re = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            MyTool.close();
        }
        return re;
    }

    public int delete(String username){
        int re = 0;
        String sql = "delete from user_info where username=?";
        try {
            PreparedStatement ps = MyTool.createPreStatement(sql);
            ps.setString(1, username);
            re = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            MyTool.close();
        }
        return re;
    }

    public int delete(int id){
        int re = 0;
        String sql = "delete from user_info where uid=?";
        try {
            PreparedStatement ps = MyTool.createPreStatement(sql);
            ps.setInt(1, id);
            re = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            MyTool.close();
        }
        return re;
    }

    /**
    * update username and password of user with uid
    */
    public int update(int uid, String username, String password){
        int re = 0;
        String sql = "update user_info set " +
                "username=?, " +
                "password=? where uid=?";
        try {
            PreparedStatement ps = MyTool.createPreStatement(sql);
            ps.setString(1, username);
            ps.setString(2,password);
            ps.setInt(3,uid);
            re = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            MyTool.close();
        }
        return re;
    }

    public ResultSet findAll(){ //可以改进，往下看
        ResultSet rs = null;
        String sql = "select * from user_info";
        try {
            PreparedStatement ps = MyTool.createPreStatement(sql);
            rs = ps.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            MyTool.close(rs);
        }
        return rs;
    }
}

7 实体类

实体类描述了某一张数据表的结构，它的作用就是存放JDBC查询结果的数据，一个实体类的实例就代表JDBC查询结果的一行，一个实体类数组可以用来存放一整个ResultSet。

【规范】

先创建实体类包，名称叫entity，与Dao包是并列关系。

在包内定义各个表的实体类，以下表为例

实体类名与数据表名一致。本例为user_info，实体类名为User_info
实体类的属性与数据表字段一致

实现了实体类后，可以对DAO封装的查询方法进行改进，这样后续调用DAO的方法时就完全不用与数据库相关的方法或者类进行交互了。

public ArrayList<User_info> findAll(){
    ResultSet rs = null;
    ArrayList<User_info> allUser = new ArrayList<>();
    String sql = "select * from user_info";
    try {
        PreparedStatement ps = MyTool.createPreStatement(sql);
        rs = ps.executeQuery();
        while(rs.next()){
            int uid = rs.getInt(1);
            String username = rs.getString(2);
            String password = rs.getString(3);
            java.sql.Date date = rs.getDate(4);
            User_info tmp = new User_info(uid,username,password,date);
            allUser.add(tmp);
        }
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    } finally {
        MyTool.close(rs);
    }
    return allUser;
}