本贴介绍DriverManager,Connection,Statement,ResultSet四个API
文章目录
DriverManager
数据库准备略
package com.gable.JDBC;
import org.junit.jupiter.api.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class jdbc_demo02_driverManager {
@Test
public void testDriver() throws Exception {
//1.注册驱动,MySQL5以后的驱动包不再需要这一步,会自动加载
// Class.forName("com.mysql.cj.jdbc.Driver");
//2.获取连接对象
String url = "jdbc:mysql:///my_temp?useSSL=false";
String uName = "root" ;
String pWord = "1234" ;
//DriverManager的第一个作用是注册驱动,现在自动了,第二个作用就是获取连接对象
Connection con = DriverManager.getConnection(url,uName,pWord);
//此处我们提前为下面要写的代码添加一条数据ada
String sql_st1 = "insert into accounts (name) values ('Ada')";
String sql_st2 = "update accounts set balance = 2000";
Statement stt = con.createStatement();
int count1 = stt.executeUpdate(sql_st1);
int count2 = stt.executeUpdate(sql_st2);
//处理结果
System.out.println(count1);
System.out.println(count2);
//释放资源,按逻辑顺序
stt.close();
con.close();
}
}
Connection
@Test
public void testConnection() throws SQLException {
String url = "jdbc:mysql:///my_temp?useSSL=false";
String uName = "root" ;
String pWord = "1234" ;
Connection con = DriverManager.getConnection(url,uName,pWord);
String sql_st1 = "update accounts set balance = 3000 where id = 1";
String sql_st2 = "update accounts set balance = 3000 where id = 2";
// Connection两个功能,第一,获取执行sql的对象,本处为statement
Statement stt = con.createStatement();
try {
// Connection两个功能,第二,事务管理,Transaction
con.setAutoCommit(false);
//因为mysql语句都是自动commit,设置为false表示这里start了一个Transaction,要手动commit
int count1 = stt.executeUpdate(sql_st1);
int i = 10/0;//注释调节此处,测试con管理事务的效果
int count2 = stt.executeUpdate(sql_st2);
con.commit();
//处理结果
System.out.println(count1);
System.out.println(count2);
} catch (SQLException e) {
con.rollback();
e.printStackTrace();
} finally {
//释放资源,按逻辑顺序
stt.close();
con.close();
}
}
数据库里可以直接点击表格修改数据,submit
Statement
@Test
public void testStatement_DML() throws Exception {
String url = "jdbc:mysql:///my_temp?useSSL=false";
String uName = "root" ;
String pWord = "1234" ;
Connection con = DriverManager.getConnection(url,uName,pWord);
String sql_st = "update accounts set balance = 3000";
Statement stt = con.createStatement();
//Statement对象执行DML语句,executeUpdate,返回执行所影响的行数
int count = stt.executeUpdate(sql_st);
//实际中我们并不需要知道这个返回值,用户更希望看到修改成功还是失败,所以
if (count > 0){
System.out.println("Modify succeed.");
} else {
System.out.println("Modify failed.");
}
stt.close();
con.close();
}
@Test
public void testStatement_DDL() throws Exception {
String url = "jdbc:mysql:///my_temp?useSSL=false";
String uName = "root" ;
String pWord = "1234" ;
Connection con = DriverManager.getConnection(url,uName,pWord);
// String sql_st = "create database my_temp2";//先用这句创一个库
String sql_st = "drop database if exists my_temp2";//再用这句执行删除
Statement stt = con.createStatement();
int count = stt.executeUpdate(sql_st);//删库成功,返回int为0
/**Statement对象执行DDL语句,executeUpdate,执行后返回值可能是1,可能是0
* 所以不能简单用返回值判断,输出成功或失败结果了*/
System.out.println(count);
stt.close();
con.close();
}
Statement执行DQL
@Test
public void testStatement_DQL() throws Exception {
String url = "jdbc:mysql:///my_temp?useSSL=false";
String uName = "root" ;
String pWord = "1234" ;
Connection con = DriverManager.getConnection(url,uName,pWord);
String sql_st = "select * from accounts;";
Statement stt = con.createStatement();
ResultSet rs = stt.executeQuery(sql_st);
/**
* Statement对象执行DQL语句,executeQuery,返回结果是一个ResultSet对象
* 这个对象又有方法,可以获得返回值
* rs.next()获得boolean,表示表的一行是否还有数据
* rs.get<T>()获得列和数据
* 也就是用ResultSet,可以获得表的全部数据,需要用一个while循环来实现
* 本例我们将表数据获取后封装到对象里输出
*/
List<Account> As = new ArrayList<>();
while (rs.next()){
int id = rs.getInt("id");//可以写列序号从1开始,或者列名
String name = rs.getString(2);
double balance = rs.getDouble("balance");
As.add(new Account(id,name,balance));
}
rs.close();
stt.close();
con.close();
//用for打印比直接一句话打印As看起来漂亮点
for (Account a : As) {
System.out.println(a);
}
}
用到的账户类
public class Account {
private int id;
private String name;
private double balance;
public Account(int id, String name, double balance) {
this.id = id;
this.name = name;
this.balance = balance;
}
public Account() {
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getBalance() {
return balance;
}
public void setBalance(double balance) {
this.balance = balance;
}
@Override
public String toString() {
return "Account{" +
"id=" + id +
", name='" + name + '\'' +
", balance=" + balance +
'}';
}
}
结果图