一 JDBC简介
1 JDBC是什么
Java Database Connectivity
为了简化、统一对数据库的操作,java中定义了一套操作数据库的规范,称之为JDBC。
JDBC提供了一套用于执行SQL语句的API,通过该API可以实现对数据库的增删改查操作。
JDBC主要由接口组成。组成JDBC的2个包:
java.sql 、javax.sql
以上2个包已经包含在J2SE中,所以不用导入,做数据库开发只需要导入JDBC的实现类即数据库驱动包。
2 JDBC开发中涉及的主要接口和类
Drive接口: 连接数据库的驱动的API
DriverManage类:驱动管理类,负责驱动的注册、获取连接等操作
Statement接口:负责sql语句的执行
--PreparedStatement接口:负责sql语句的预处理
--CallableStatement接口:负责处理数据库中存储过程、函数等
ResultSet接口:处理查询数据库的结果
3 JDBC架构
不同种类的数据库在其内部处理数据的方式是不同的。没有JDBC时,如果直接使用数据库厂商提供的API操作数据库,应用程序的可移植性会变差。比如用户使用MySQL数据库,那么程序中就需要使用MySQL提供的API操作数据库。如果换成其他数据库,比如Oracle,那么就需要使用Oracle提供的API操作,这样,可能会改动大量代码。
为了避免这种情况,各个数据库厂商提供的API统一实现JDBC中提供的接口,具体的操作,由各个厂商实现,这使得代码的通用性更强。
二 通过JDBC操作MySQL数据库
基本步骤:
导入数据库驱动jar包
获取连接对象
创建Statement对象
执行sql语句
关闭相关对象
1 创建数据库连接
(1) 导入mysql的驱动文件
数据库版本不同,导入的驱动不同
mysql8.0及以上导入:mysql-connector-java-8.0.16.jar
mysql8.0以下导入:mysql-connector-java-5.1.46.jar
注:mysql-connector-java-8.0.XXX.jar向下兼容mysql5.7的数据库
导入流程:
项目中新建lib文件夹,将mysql的驱动文件拷贝到该文件夹下
在lib目录上右键->Add as Library...
在Project Structure目录下查看
(2) 数据库连接URL
URL 统一资源定位
基本格式:
协议:子协议://ip:端口号/数据库?参数
常用参数:
useUnicode=true 是否使用Unicode字符集
characterEncoding=UTF-8 指定编码方式
useSSL=false 和数据库通信时是否使用SSL(安全套接字协议)
serverTimezone=Asia/Shanghai 指定时区,如果使用mysql8的驱动,必须指定该参数
等等
注:mysql默认端口号3306
mysql8.0及以上连接url:
jdbc:mysql://localhost:3306/db2206?serverTimezone=Asia/Shanghai
注意:serverTimezone 必须指定
mysql8.0以下连接url:
jdbc:mysql://localhost:3306/db2206
(3) 创建连接
mysql8.0及以上驱动类名:com.mysql.cj.jdbc.Driver
mysql8.0以下驱动类名:com.mysql.jdbc.Driver
使用类:DriverManager
常用方法:
registerDriver(Driver driver) 注册驱动
getConnection(String url, String user, String pwd) 建立与数据库连接,返回连接对象
注意:连接对象使用完后,一定要关闭连接,释放资源
// 方案1
public void connection1(){
try {
Driver driver = new com.mysql.cj.jdbc.Driver();
//注册驱动
DriverManager.registerDriver(driver);
String url = "jdbc:mysql://localhost:3306/db2206?serverTimezone=Asia/Shanghai";
//通过DriverManager获取连接对象
Connection conn = DriverManager.getConnection(url, "root", "root");
System.out.println(conn);
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//方案2
public void connection2(){
try {
//注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/db2206?serverTimezone=Asia/Shanghai";
//通过DriverManager获取连接对象
Connection conn = DriverManager.getConnection(url, "root", "root");
System.out.println(conn);
conn.close();
} catch (ClassNotFoundException | SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//方案3
public void connection3(){
try {
// 读取properties文件
InputStream in = new FileInputStream("src/db.properties");
Properties pro = new Properties();
// 加载文件
pro.load(in);
// 关闭流
in.close();
// 从属性对象中读取数据,并使用
Class.forName(pro.get("driver").toString());
Connection conn = DriverManager.getConnection(pro.get("url").toString(), pro.get("user").toString(), pro.get("password").toString());
System.out.println(conn);
conn.close();
} catch (ClassNotFoundException | SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
db.properties内容如下:
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai
user=root
password=root
2 通过Statement执行sql语句
通过Statement对象可以向数据库发送SQL语句,执行SQL语句,并返回执行结果
该对象通过Connection的createStatement()方法创建
主要方法:
executeUpdate(String sql) 执行增删改的SQL语句,返回int类型的值,表示SQL语句影响的行数executeQUery(String sql) 执行查询的SQL语句,返回一个ResultSet对象
(1) 插入/删除/修改数据
// 添加数据
public static void addInfo(){
Connection conn = null;
Statement statement = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/db2206?serverTimezone=Asia/Shanghai";
conn = DriverManager.getConnection(url, "root", "root");
statement = conn.createStatement();
//增删改的时候统统使用executeUpdate
statement.executeUpdate("insert into person(name, age) values('zhangsan', 20)");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
//关闭相关对象
try {
statement.close();
} catch (SQLException e) {}
try {
conn.close();
} catch (SQLException e) {}
}
}
// 使用参数的情况
public static void addInfo(String name, Integer age) throws Exception{
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/db2206?serverTimezone=Asia/Shanghai";
Connection connection = DriverManager.getConnection(url, "root", "root");
Statement statement = connection.createStatement();
//如果有参数,需要进行字符串的拼接,生成sql语句
String sql = "insert into person(name, age) values('" + name + "', " + age + ")";
statement.executeUpdate(sql);
statement.close();
connection.close();
}
// 更新数据
public static void updateInfo(){
Connection connection = JdbcUtil.getConnection();
Statement statement = null;
try {
statement = connection.createStatement();
statement.executeUpdate("update person set name='haha' where id=8");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JdbcUtil.close(connection, statement);
}
}
// 删除数据
public static void deleteInfo(){
Connection connection = JdbcUtil.getConnection();
Statement statement = null;
try {
statement = connection.createStatement();
statement.executeUpdate("delete from person where id=2");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JdbcUtil.close(connection, statement);
}
}
JDBCUtil的封装:
package com.glls.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* 针对数据库的工具类
* @author renrui
*
*/
public class JdbcUtils {
// 静态代码块只会执行一次
static {
try {
// 注册驱动,只需要注册一次
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 获取连接对象
* @return
*/
public static Connection getConnection() {
String url = "jdbc:mysql://localhost:3306/db2206?serverTimezone=Asia/Shanghai&useSSL=false";
try {
return DriverManager.getConnection(url, "root", "root");
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 关闭相关对象
* @param conn
* @param statement
*/
public static void close(Connection conn, Statement statement) {
if(statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 关闭各个对象
* @param conn
* @param statement
* @param rs
*/
public static void close(Connection conn, Statement statement, ResultSet rs) {
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
(2) 查询数据
返回ResultSet对象,表示结果集。需要对结果集进行处理,获取需要得数据
常用方法:
next() 从前向后逐个获取结果集中的数据
getString(int columnIndex) 根据字段索引获取指定字段的String类型的值
getString(String columnName) 根据字段名称获取指定字段的String类型的值
.....
public static void findInfo() throws SQLException{
Connection connection = JdbcUtil.getConnection();
Statement statement = connection.createStatement();
//查询使用executeQuery
//执行查询操作,返回一个结果集对象
ResultSet rs = statement.executeQuery("select * from person");
//判断是否有下一条数据
while(rs.next()){
//根据返回数据的索引获取字段中数据,从1开始
//System.out.println(rs.getInt(1));
//System.out.println(rs.getString(2));
//System.out.println(rs.getInt(3));
//根据字段名获取数据
System.out.println(rs.getInt("id"));
System.out.println(rs.getString("name"));
System.out.println(rs.getInt("age"));
}
JdbcUtil.close(connection, statement, rs);
}
SQL类型 | Jdbc对应方法 | 返回类型 |
---|---|---|
BIT(1) bit(n) | getBoolean/getByte() | Boolean byte[] |
TINYINT | getByte() | Byte |
SMALLINT | getShort() | Short |
Int | getInt() | Int |
BIGINT | getLong() | Long |
CHAR,VARCHAR,LONGVARCHAR | getString() | String |
Text(clob) Blob | getClob/getBlob() | Clob Blob |
DATE | getDate() | java.sql.Date |
TIME | getTime() | java.sql.Time |
TIMESTAMP | getTimestamp() | java.sql.Timestamp |
3 通过PreparedStatement执行sql语
PreperedStatement是Statement的子接口,它的实例对象通过Connection对象.preparedStatement()方法获得。
主要方法:
executeUpdate() 主要用于执行增删改相关SQL语句
executeQuery() 执行查询的SQL语句
setString(int index, String v) 根据参数索引值设置String类型参数,索引从1开始
。。。。。
使用PreperedStatement的好处?
PreperedStatement可以避免SQL注入的问题;
Statement会使数据库频繁编译SQL,可能造成数据库缓冲区溢出,而PreparedStatement 可对SQL进行预编译,从而提高数据库的执行效率;
PreperedStatement对于sql中的参数,允许使用占位符的形式进行替换,简化sql语句的编写
public void add(Person person){
Connection connection = JdbcUtil.getConnection();
String sql = "insert into person(name,age) values(?,?)";
PreparedStatement statement = null;
try {
statement = connection.prepareStatement(sql);
statement.setInt(2, person.getAge());
statement.setString(1, person.getName());
statement.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JdbcUtil.close(connection, statement, null);
}
}
public void deleteById(int id){
Connection connection = JdbcUtil.getConnection();
String sql = "delete from person where id=?";
PreparedStatement statement = null;
try {
statement = connection.prepareStatement(sql);
statement.setInt(1, id);
statement.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JdbcUtil.close(connection, statement, null);
}
}
public void update(Person p){
Connection connection = JdbcUtil.getConnection();
String sql = "udpate person set name=?,age=? where id=?";
PreparedStatement statement = null;
try {
statement = connection.prepareStatement(sql);
statement.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JdbcUtil.close(connection, statement, null);
}
}
/**
*
* @return
*/
public List<Person> findAll(){
Connection connection = JdbcUtil.getConnection();
String sql = "select * from person";
PreparedStatement statement = null;
ResultSet rs = null;
List<Person> list = new ArrayList<>();
try {
statement = connection.prepareStatement(sql);
rs = statement.executeQuery();
while(rs.next()){
Person p = new Person();
p.setId( rs.getInt("id"));
p.setName(rs.getString("name"));
p.setAge(rs.getInt("age"));
list.add(p);
}
return list;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JdbcUtil.close(null, statement, rs);
}
return null;
}
public Person findByID(int id){
Connection connection = JdbcUtil.getConnection();
String sql = "select * from person where id=?";
PreparedStatement statement = null;
ResultSet rs = null;
Person p = new Person();
try {
statement = connection.prepareStatement(sql);
//执行之前设置参数
statement.setInt(1, id);
rs = statement.executeQuery();
if(rs.next()){
p.setId( rs.getInt("id"));
p.setName(rs.getString("name"));
p.setAge(rs.getInt("age"));
}
return p;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JdbcUtil.close(null, statement, rs);
}
return null;
}
三 其他操作
1 获取自增长的字段的值
方法:prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)
public static void getAutoIncrementValue(){
Connection connection = JdbcUtil.getConnection();
String sql = "insert into person(name, age) values('zhangsan', 3)";
PreparedStatement statement = null;
ResultSet generatedKeys = null;
try {
//设置Statement.RETURN_GENERATED_KEYS参数
statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
statement.executeUpdate();
//得到生成自增长的值
generatedKeys = statement.getGeneratedKeys();
if(generatedKeys.next()){
int v = generatedKeys.getInt(1);
System.out.println(v);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JdbcUtil.close(connection, statement, generatedKeys);
}
}
2 JDBC中使用事务
默认情况下,Connection对象会自动向数据库提交在它上面发送的SQL语句
如果需要使用事务,需要关闭自动提交
事务操作需要执行Connection对象中方法:
setAutoCommit(false); 设置不自动提交,相当于开启事务
commit() 提交
rollback() 回滚
public class Demo {
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection connection = JdbcUtils.getConnection();
PreparedStatement statement1 = null;
PreparedStatement statement2 = null;
try {
// 开始事务,设置为手动提交
connection.setAutoCommit(false);
String sql1 = "update account set money=money-1000 where name='zhangsan'";
String sql2 = "update1 account set money=money+1000 where name='lisi'";
statement1 = connection.prepareStatement(sql1);
statement1.executeUpdate();
statement2 = connection.prepareStatement(sql2);
statement2.executeUpdate();
// 提交
connection.commit();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
try {
// 回滚
connection.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}finally{
JdbcUtils.close(connection, statement1, null);
try {
statement2.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
3 批处理
主要方法:
addBatch() 添加批处理操作
executeBatch() 执行批处理操作
clearBatch() 清理
批处理如果生效,连接的url中需要增加如下参数:
# 使用该配置后,会将多条语句合并成一条语句,一次性提交给服务器
# 比如插入时,会对批量的数据重新组装成 insert into XXX(...) values(...),(...)
rewriteBatchedStatements=true
public static void batch(){
long begin = System.currentTimeMillis();
Connection connection = JdbcUtils.getConnection();
String sql = "insert into person(name,age) values(?,?)";
PreparedStatement statement = null;
try {
statement = connection.prepareStatement(sql);
int flag = 0;
for(int i = 100; i < 10000; i++){
statement.setString(1, "Jerry" + i);
statement.setInt(2, i);
//每100条sql语句,批处理一次
flag++;
statement.addBatch();
if(flag % 100 == 0){
statement.executeBatch();
statement.clearBatch();
flag = 0;
}
}
if(flag > 0){
statement.executeBatch();
statement.clearBatch();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JdbcUtils.close(connection, statement, null);
}
long end = System.currentTimeMillis();
// 批处理不生效时,54810毫秒
// 使用rewriteBatchedStatements=true参数,1368毫秒
System.out.println(end - begin);
}
开启事务
public static void batch2(){
long begin = System.currentTimeMillis();
Connection connection = JdbcUtils.getConnection();
String sql = "insert into person(name,age) values(?,?)";
PreparedStatement statement = null;
try {
// 开启事务
connection.setAutoCommit(false);
statement = connection.prepareStatement(sql);
int flag = 0;
for(int i = 100; i < 10000; i++){
statement.setString(1, "Jerry" + i);
statement.setInt(2, i);
//每100条sql语句,批处理一次
flag++;
statement.addBatch();
if(flag % 100 == 0){
statement.executeBatch();
statement.clearBatch();
flag = 0;
}
}
if(flag > 0){
statement.executeBatch();
statement.clearBatch();
}
// 提交
connection.commit();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JdbcUtils.close(connection, statement, null);
}
long end = System.currentTimeMillis();
// 批处理生效,并开启事务,753毫秒
System.out.println(end - begin);
}
四 对JDBC的封装 了解
前面进行的数据库操作,很多代码是重复的。比如增删改,整个流程相似,只是sql语句和参数不同,对此,我们可以对代码进行进一步封装。
1 BeanUtils组件
Apache提供,用来方便的操作java实体类对象
需要导入如下两个jar文件:
commons-logging-1.1.3.jar
commons-beanutils-1.8.3.jar
public static void test1(){
User u = new User();
u.setName("zhangsan");
try {
//通过BeanUtils给对象中的属性赋值
//本质上,"age"表示实体类中的getAge和setAge方法
BeanUtils.setProperty(u, "age", 20);
System.out.println(u.getName());
System.out.println(u.getAge());
Class clz = Class.forName("com.rr.beanutil.User");
Object obj = clz.newInstance();
BeanUtils.setProperty(obj, "name", "lisi");
//给属性赋值
BeanUtils.copyProperty(obj, "age", 30);
System.out.println(BeanUtils.getProperty(obj, "name"));
//对象间属性的值的拷贝
BeanUtils.copyProperties(obj, u);
System.out.println(obj);
} catch (IllegalAccessException | InvocationTargetException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
2 封装
/**
* 针对增删改
* @param sql
* @param params
*/
public void update(String sql, Object[] params){
if(sql == null || sql.isEmpty()){
return;
}
Connection conn = JdbcUtil.getConnection();
PreparedStatement statement = null;
try {
statement = conn.prepareStatement(sql);
//参数的元数据,获取参数个数
int pCount = statement.getParameterMetaData().getParameterCount();
if(pCount > 0){
//判断参数
if(params != null){
int count = params.length;
if(pCount != count){
throw new RuntimeException("参数个数不符");
}else{
for(int i = 0; i < pCount; i++){
statement.setObject(i + 1, params[i]);
}
}
}else{
throw new RuntimeException("参数个数不符");
}
}
statement.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JdbcUtil.close(conn, statement, null);
}
}
//泛型方法
public <T> List<T> query(String sql, Object[] params, Class<T> clz){
if(sql == null || sql.isEmpty()){
return null;
}
Connection conn = JdbcUtil.getConnection();
PreparedStatement statement = null;
ResultSet rs = null;
T t = null;
List<T> list = new ArrayList<>();
try {
statement = conn.prepareStatement(sql);
//参数的元数据,获取参数个数
int pCount = statement.getParameterMetaData().getParameterCount();
if(pCount > 0){
//判断参数
if(params != null){
int count = params.length;
if(pCount != count){
throw new RuntimeException("参数个数不符");
}else{
for(int i = 0; i < pCount; i++){
statement.setObject(i + 1, params[i]);
}
}
}else{
throw new RuntimeException("参数个数不符");
}
}
rs = statement.executeQuery();
//结果集的元数据
ResultSetMetaData metaData = rs.getMetaData();
//得到查询的数据有多少个字段
int cCount = metaData.getColumnCount();
while(rs.next()){
//创建实体类的对象
t = clz.newInstance();
for(int i = 0; i < cCount; i++){
//得到字段名称
String name = metaData.getColumnName(i +1);
//根据列名得到值数据
Object value = rs.getObject(name);
BeanUtils.copyProperty(t, name, value);
}
list.add(t);
}
return list;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InvocationTargetException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JdbcUtil.close(conn, statement, rs);
}
return null;
}
五 通过DBUtils操作数据库
Apache提供,对jdbc的封装,可以简化数据库操作。
轻量级的ORM(对象关系映射)框架
主要类和接口:
QueryRunner类,用于执行数据库操作
主要方法:query()方法,用于执行查询
update() 方法,用于执行增删改
ResultSetHandler接口,用于处理Result结果集
实现类:BeanHandler 将结果集中的第一行数据封装到实体类对象中
BeanListHandler 将每一行数据封装到实体类对象中,并放到List
ScalarHandler 将结果集中第一条记录的某列数据存储为Object对象
public static void query1(){
Connection conn = JdbcUtil.getConnection();
//通过该对象进行数据库操作
QueryRunner qr = new QueryRunner();
String sql = "select * from user where id=2";
try {
User user = qr.query(conn, sql, new ResultSetHandler<User>() {
@Override
public User handle(ResultSet rs) throws SQLException {
// TODO Auto-generated method stub
User u = null;
if(rs.next()){
u = new User();
u.setId(rs.getInt("id"));
u.setUsername(rs.getString("username"));
u.setPassword(rs.getString("password"));
}
return u;
}
});
System.out.println(user.getUsername());
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally{
JdbcUtil.close(conn, null, null);
}
}
public static void query2(){
Connection connection = JdbcUtil.getConnection();
QueryRunner qr = new QueryRunner();
String sql = "select * from user where id=2";
try {
//获取查询到的第一行数据,BeanHandler内部将查询出的用户数据封装到User对象中,并返回
//表中字段的名称必须和实体类型属性名相同
User user = qr.query(connection, sql, new BeanHandler<>(User.class));
System.out.println(user.getUsername());
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JdbcUtil.close(connection, null, null);
}
}
public static void query3(){
Connection connection = JdbcUtil.getConnection();
QueryRunner qr = new QueryRunner();
String sql = "select * from user";
try {
//返回集合数据,将查询到的数据封装到List集合中
List<User> list = qr.query(connection, sql, new BeanListHandler<>(User.class));
for (User user : list) {
System.out.println(user.getUsername());
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JdbcUtil.close(connection, null, null);
}
}
public static void query4(){
Connection connection = JdbcUtil.getConnection();
QueryRunner qr = new QueryRunner();
String sql = "select * from user where id=?";
try {
//获取查询到的第一行数据,BeanHandler内部将查询出的用户数据封装到User对象中,并返回
//表中字段的名称必须和实体类型属性名相同
//通过query方法向sql中的参数传参时,对应类型是 Object...
User user = qr.query(connection, sql, new BeanHandler<>(User.class), 2);
System.out.println(user.getUsername());
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JdbcUtil.close(connection, null, null);
}
}
public static void query5(){
Connection connection = JdbcUtil.getConnection();
QueryRunner qr = new QueryRunner();
String sql = "select * from user where id=2";
try {
//将查询到的数据放到Object数组中
Object[] arr = qr.query(connection, sql, new ArrayHandler());
System.out.println(Arrays.toString(arr));//[2, lisi, 1234]
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JdbcUtil.close(connection, null, null);
}
}
public static void query6(){
Connection connection = JdbcUtil.getConnection();
QueryRunner qr = new QueryRunner();
String sql = "select * from user";
try {
//将查询到的数据放到Object数组集合中
List<Object[]> arrList = qr.query(connection, sql, new ArrayListHandler());
for (Object[] objects : arrList) {
System.out.println(Arrays.toString(objects));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JdbcUtil.close(connection, null, null);
}
}
public static void query7(){
Connection connection = JdbcUtil.getConnection();
QueryRunner qr = new QueryRunner();
String sql = "select count(*) from user";
try {
//如果不指定参数,表示获得查询结果中第一行第一列的数据
Long count = qr.query(connection, sql, new ScalarHandler<Long>());
System.out.println(count);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JdbcUtil.close(connection, null, null);
}
}
public static void insertInfo(){
Connection connection = JdbcUtil.getConnection();
QueryRunner qr = new QueryRunner();
String sql = "insert into user(username,password) values(?,?)";
try {
//update执行增删改
// qr.update(connection, sql, "haha", "123123");
Object[] params = new Object[]{"aaa", "bbbb"};
qr.update(connection, sql, params);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally{
JdbcUtil.close(connection, null, null);
}
}