JDBC
JDBC常用接口
1.Driver接口:
加载驱动类
Class.forName(“com.mysql.jdbc.Driver”);
MySQL 8.0修改为:
Class.forName(“com.mysql.cj.jdbc.Driver”);
2.DriverManager接口:
3.Connection接口:
- 建立连接(连接对象内部其实包含了Socket对象,是一个远程的连接。比较耗时!这是Connection对象管理的一个要点!)
真正开发中,为了提高效率,都会使用连接池来管理连接对象!
String url = "jdbc:mysql://localhost:3306/testjdbc";
//jdbc:mysql://主机名:端口号/数据库名
String user = "root";
String password = "123456";
Connection con = DriverManager.getConnection(url, user, password);
MySQL 8.0需要添加时区
String url = "jdbc:mysql://localhost:3306/testjdbc?serverTimezone=UTC";
String user = "root";
String password = "123456";
Connection con = DriverManager.getConnection(url, user, password);
4.Statement接口:
测试Statement接口的用法,执行SQL语句,以及SQL注入问题
Statement stmt = conn.createStatement();
//容易发生SQL注入
String sql = "insert into t_user (username,pwd,regTime) values ('eee',2333,now())";
stmt.execute(sql);
String id = "5 or 1=1";//后者相当于true
String sql1 = "delete from t_user where id ="+id; //发生SQL注入,全部删除
stmt.execute(sql1);
测试PreparedStatement的基本用法
String sql = "insert into t_user (username,pwd) values (?,?)";
PreparedStatement ps = con.prepareStatement(sql);
//方法一、设置对应类型
ps.setString(1,"aaa");
ps.setString(2,"123456");
//方法二、设置为Object类
ps.setObject(1, "aaa");
ps.setObject(2, "123456");
//ps.execute();
int count = ps.executeUpdate(); //返回更新的行数
5.ResultSet接口:
测试ResultSet结果集的基本用法
String sql = "select id,username,pwd from t_user where id>?";//?占位符
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, 2);//id大于2的取出来
ResultSet rs = ps.executeQuery();
while(rs.next()) {
System.out.println(rs.getInt(1)+"----"+rs.getString(2)+"----"+rs.getString(3));
}
- 依序关闭使用之对象与连接,后开的先关闭,resultset–>statment–>connection,一定要将三个try-catch块,分开写!
6.批处理Batch:
测试批处理的基本用法
con.setAutoCommit(false); //设为手动提交
//尽量使用Statement
Statement stmt = con.createStatement();
for (int i = 0; i < 20000; i++) { //提交2w条数据
String sql = "insert into t_user (username,pwd,regTime) values ('gao"+i+"',666666,now())";
stmt.addBatch(sql);
}
stmt.executeBatch();
con.commit(); //提交
7.事务:
DML(data manipulation language)数据操纵语言:UPDATE、INSERT、DELETE
DDL(data definition language)数据库定义语言:CREATE、ALTERL:修改、DROP
DCL(Data Control Language)数据库控制语言:GRANT:授权、ROLLBACK:回滚、COMMIT:提交
DQL(Data Query Language)数据库查询语言:SELECT
事务隔离级别从低到高:
- 数据未提交(Read Uncommitted)
- 读取已提交(Read Committed)
- 可重复读(Repeatable Read)
- 序列化(serializable)
con.setAutoCommit(false);// JDBC默认为自动提交事务
ps1 = con.prepareStatement("insert into t_user (username,pwd) values (?,?)");
ps1.setObject(1, "Ming");
ps1.setObject(2, "123456");
ps1.execute();
System.out.println("插入一个用户,小明");
//执行失败,两条语句均执行失败
ps2 = con.prepareStatement("insert into t_user (username,pwd) values (?,?,?)");
ps2.setObject(1, "Gang");
ps2.setObject(2, "123456");
ps2.execute();
System.out.println("插入一个用户,小刚 ");
con.commit();
con.rollback(); //回滚
8.时间类型
ps.setDate(3, date);
ps.setTimestamp(4, stamp);
9.CLOB
输出:
PreparedStatement ps = con.prepareStatement("insert into t_user (username,myInfo) values (?,?)");
ps.setString(1, "abc");
//将文本文件内容输入到数据库中
ps.setClob(2, new FileReader(new File("F:\\JAVA\\CLOB.txt")));
//将字符串输入到数据库中
ps.setClob(2, new BufferedReader(new InputStreamReader(new ByteArrayInputStream("aaa".getBytes()))));44
输入 :
PreparedStatement ps = con.prepareStatement("select * from t_user where id=?");
ps.setObject(1, 2021);//读取id为2021的
ResultSet rs = ps.executeQuery();
while(rs.next()) {
Clob c= rs.getClob("myInfo");
Reader r = c.getCharacterStream();
int len = 0;
while((len=r.read())!=-1) {
System.out.println((char)len);
}
}
10.BLOB
输出:
PreparedStatement ps = con.prepareStatement("insert into t_user (username,headIMG) values (?,?)");
ps.setString(1, "abc");
ps.setBlob(2, new FileInputStream(new File("F:\\JAVA\\BLOB.jpeg")));
输入:
PreparedStatement ps = con.prepareStatement("select * from t_user where id=?");
ps.setObject(1, 2022);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
Blob b = rs.getBlob("headIMG");
InputStream is = b.getBinaryStream();
OutputStreamos = new FileOutputStream(new File("F:\\JAVA\\BLOB_saved.jpeg"));
int len = 0;
while((len=is.read())!=-1) {
os.write(len);
}
}