概述
package com.atguigu2.statement.crud;
public class User {
private String user;
private String password;
public User() {
}
public User(String user, String password) {
this.user = user;
this.password = password;
}
public String toString() {
return "User [user=" + this.user + ", password=" + this.password + "]";
}
public String getUser() {
return this.user;
}
public void setUser(String user) {
this.user = user;
}
public String getPassword() {
return this.password;
}
public void setPassword(String password) {
this.password = password;
}
}
package com.atguigu2.statement.crud;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.Scanner;
import org.junit.Test;
public class StatementTest {
public StatementTest() {
}
// 使用 Statement 的弊端，需要拼写 sql 语句，并且存在 SQL 注入的问题
// 如何避免出现 sql 注入：只要用 PreparedStatement (从 Statement 扩展而来) 取代 Statement
@Test
public void testLogin() {
Scanner scanner = new Scanner(System.in);
System.out.print("请输入用户名：");
String user = scanner.nextLine();
System.out.print("请输入密码：");
String password = scanner.nextLine();
String sql = "SELECT user,password FROM user_table WHERE user = '" + user + "' AND password = '" + password + "'";
User returnUser = (User)this.get(sql, User.class);
if (returnUser != null) {
System.out.println("登录成功");
} else {
System.out.println("用户名不存在或密码错误");
}
}
// 使用 Statement 实现对数据表的查询操作
public <T> T get(String sql, Class<T> clazz) {
T t = null;
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
//1.加载配置文件
InputStream is = StatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
//2.读取配置信息
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
//3.加载驱动
Class.forName(driverClass);
conn = DriverManager.getConnection(url, user, password);
st = conn.createStatement();
rs = st.executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
if (!rs.next()) {
return null;
} else {
t = clazz.newInstance();
for(int i = 0; i < columnCount; ++i) {
String columnName = rsmd.getColumnLabel(i + 1);
Object columnVal = rs.getObject(columnName);
Field field = clazz.getDeclaredField(columnName);
field.setAccessible(true);
field.set(t, columnVal);
}
Object var20 = t;
return (T) var20;
}
} catch (Exception var39) {
var39.printStackTrace();
return null;
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException var38) {
var38.printStackTrace();
}
}
if (st != null) {
try {
st.close();
} catch (SQLException var37) {
var37.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException var36) {
var36.printStackTrace();
}
}
}
}
}
package com.atguigu3.preparedstatement.crud;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Properties;
import org.junit.Test;
import com.atguigu1.connection.ConnectionTest;
import com.atguigu3.util.JDBCUtils;
/*
* 使用 PreparedStatement 来替换 Statement，实现对数据表的增删改查操作
*
* 增删改：查
*
*/
public class PreparedStatementUpdateTest {
@Test
public void testCommonUpdate() {
// String sql = "delete from customers where id = ?";
// update(sql,3);
// 报错，表名不能用关键字命名，否则执行的时候报错，order 表
// String sql = "update order set order_name = ? where order_id = ?";
// update(sql,"DD","2");
String sql = "update `order` set order_name = ? where order_id = ?";// "`" 飘号(不是单引号)：在键盘 1 的左边
update(sql,"DD","2");
}
// 通用的 增删改查 操作 (增删改)
public void update(String sql,Object ...args){ // sql 中的占位符的个数与可变形参的长度相同
Connection conn = null;
PreparedStatement ps = null;
try {
//1.获取数据库的连接
conn = JDBCUtils.getConnection();
//2.预编译 sql 语句，返回 PreparedStatement 的实例
ps = conn.prepareStatement(sql);
//3.填充占位符
for(int i = 0; i < args.length; i++) {
ps.setObject(i+1, args[i]);//小心参数声明错误！
}
//4.执行
ps.execute();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
//5.资源的关闭
JDBCUtils.closeResource(conn, ps);
}
}
// 向 customers 表中添加一条记录
@Test
public void testInsert() {
//3.获取链接
Connection conn = null;
PreparedStatement ps = null;
try {
//1.读取配置文件中的 4 个基本信息
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
//2.加载驱动
Class.forName(driverClass);
//3.获取连接
conn = DriverManager.getConnection(url,user,password);
System.out.println(conn);
//4.预编译 sql 语句，返回 PreparedStatement 的实例
String sql = "insert into customers(name,email,birth)values(?,?,?)";// ? : 占位符
ps = conn.prepareStatement(sql);
//5.填充占位符
ps.setString(1, "哪吒");
ps.setString(2, "nezha@gmail.com");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date date = sdf.parse("1000-01-01");
ps.setDate(3, new Date(date.getTime()));
//6.执行操作
ps.execute();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
//7.资源的关闭
try {
if(ps != null)
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(conn != null)
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
//修改 customers 表的一条记录
@Test
public void testUpdate() {
Connection conn = null;
PreparedStatement ps = null;
try {
//1.获取数据库的连接
conn = JDBCUtils.getConnection();
//2.预编译 sql 语句，返回 PreparedStatement 的实例
String sql = "update customers set name = ? where id = ?";
ps = conn.prepareStatement(sql);
//3.填充占位符
ps.setObject(1, "莫扎特");
ps.setObject(2, 18);
//4.执行
ps.execute();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
//5.资源的关闭
JDBCUtils.closeResource(conn, ps);
}
}
}
package com.atguigu3.bean;
import java.sql.Date;
/*
* ORM 编程思想(object relational mapping)
* 一个数据表对应一个 java 类
* 表中的一条记录对应 java 类的一个对象
* 表中的一个字段对应 java 类的一个属性
*
*
* Java 与 SQL 对应数据类型转换表
*
* Java 类型 SQL 类型
* boolean BIT
* byte TINYINT
* short SMALLINT
* int INTGER
* long BIGINT
* String CHAR,VARCHAR,LONGVARCHAR
* byte array BINARY,VAR BINARY
* java.sql.Date DATE
* java.sql.Time TIME
* java.sql.Timestamp TIMESTAMP
*
*/
public class Customer {
private int id;
private String name;
private String email;
private Date birth;
public Customer() {
super();
}
public Customer(int id, String name, String email, Date birth) {
super();
this.id = id;
this.name = name;
this.email = email;
this.birth = birth;
}
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 String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getBirth() {
return birth;
}
public void setBirth(Date birth) {
this.birth = birth;
}
@Override
public String toString() {
return "Customer [id=" + id + ", name=" + name + ", email=" + email + ", birth=" + birth + "]";
}
}
package com.atguigu3.util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* 操作数据库的工具类，此工具类避免每次操纵数据库时，连接数据库，关闭资源。
* @author 21115
*
*/
public class JDBCUtils {
/**
* 获取数据库的连接
* @return
* @throws Exception
*/
public static Connection getConnection() throws Exception {
//1.读取配置文件中的 4 个基本信息
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
//2.加载驱动
Class.forName(driverClass);
//3.获取连接
Connection conn = DriverManager.getConnection(url,user,password);
return conn;
}
/**
* 关闭连接和 Statement 的操作
* @param conn
* @param ps
*/
public static void closeResource(Connection conn,Statement ps) {
try {
if(ps != null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 关闭资源操作
public static void closeResource(Connection conn,Statement ps,ResultSet rs) {
try {
if(ps != null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
package com.atguigu3.preparedstatement.crud;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
import com.atguigu3.bean.Customer;
import com.atguigu3.util.JDBCUtils;
import com.mysql.cj.jdbc.result.ResultSetMetaData;
/**
* 针对于 Customers 表的查询操作
*
*/
public class CustomerForQuery {
@Test
public void testQueryForCustomers() {
String sql = "select id,name,birth,email from customers where id = ?";
Customer customer = queryForCustomers(sql,13);
System.out.println(customer);
//Customer [id=13, name=张学友, email=zhangxy@126.com, birth=1998-12-21]
sql = "select name,email from customers where name = ?";
Customer customer1 = queryForCustomers(sql,"周杰伦");
System.out.println(customer1);
//Customer [id=0, name=周杰伦, email=zhoujl@sina.com, birth=null]
}
/**
* 针对于 customers 表的通用的查询操作
*
*/
public Customer queryForCustomers(String sql,Object...args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
for(int i = 0; i < args.length; i++) {
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
// 获取结果集的元数据：ResultSetMetaData
ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
// 通过 ResultSetMetaData 获取结果集中的列数
int columnCount = rsmd.getColumnCount();
if(rs.next()) {
Customer cust = new Customer();
// 处理结果集一行数据中的每一个列
for(int i = 0; i < columnCount; i++) {
// 获取列值
Object columValue = rs.getObject(i + 1);
// 获取每个列的列名
String columnName = rsmd.getColumnName(i + 1);
// 给 cust 对象指定的 columnName 属性，赋值为 columValue：通过反射
Field field = Customer.class.getDeclaredField(columnName);
field.setAccessible(true);
field.set(cust, columValue);
}
return cust;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps, rs);
}
return null;
}
@Test
public void testQuery1() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
//执行，并返回结果集
conn = JDBCUtils.getConnection();
String sql = "select id,name,email,birth from customers where id = ?";
ps = conn.prepareStatement(sql);
ps.setObject(1, 1);
resultSet = ps.executeQuery();
//处理结果集
//next():判断结果集的下一条是否有数据，如果有数据返回 true，并指针下移；如果返回 false，指针不会下移。
if(resultSet.next()) {
//获取当前这条数据的各个字段值
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
String email = resultSet.getString(3);
Date birth = resultSet.getDate(4);
// 方式一：
// System.out.println("id = " + id + ",name = " + name + ",email = " + email + ",birth = " + birth);
// 方式二：
Object[] data = new Object[] {id,name,email,birth};
// 方式三：将数据封装为一个对象(推荐)
Customer customer = new Customer(id,name,email,birth);
System.out.println(customer);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭资源
JDBCUtils.closeResource(conn, ps, resultSet);
}
//Customer [id=1, name=汪峰, email=wf@126.com, birth=2010-02-02]
}
}
package com.atguigu3.bean;
import java.sql.Date;
public class Order {
private int orderId;
private String orderName;
private Date orderDate;
public Order() {
super();
}
public Order(int orderId, String orderName, Date orderDate) {
super();
this.orderId = orderId;
this.orderName = orderName;
this.orderDate = orderDate;
}
public int getOrderId() {
return orderId;
}
public void setOrderId(int orderId) {
this.orderId = orderId;
}
public String getOrderName() {
return orderName;
}
public void setOrderName(String orderName) {
this.orderName = orderName;
}
public Date getOrderDate() {
return orderDate;
}
public void setOrderDate(Date orderDate) {
this.orderDate = orderDate;
}
@Override
public String toString() {
return "Order [orderId=" + orderId + ", orderName=" + orderName + ", orderDate=" + orderDate + "]";
}
}
package com.atguigu3.preparedstatement.crud;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
import com.atguigu3.bean.Order;
import com.atguigu3.util.JDBCUtils;
import com.mysql.cj.jdbc.result.ResultSetMetaData;
/**
* 针对于 Order 表的通用的查询操作
*
*/
public class OrderForQuery {
/*
* 针对于表的字段名与类的属性名不相同的情况：
* 1.必须声明 sql 时，使用类的属性名来命名字段的别名
* 2.使用 ResultSetMetaData 时，需要使用 getColumnLabel() 来替换 getColumnName(),
* 获取列的别名。
*
* 说明：如果 sql 中没有给字段起别名，getColumnLabel() 获取的就是列名
*
*/
@Test
public void testOrderForQuery() {
String sql = "select order_id orderId,order_name orderName,order_date orderDate from `order` where order_id = ?";
Order order = orderForQuery(sql,1);
System.out.println(order);
// Order [orderId=1, orderName=AA, orderDate=2010-03-04]
}
/**
* 通用的针对于 order 表的查询操作
*
*/
public Order orderForQuery(String sql,Object...args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
for(int i = 0; i < args.length; i++) {
ps.setObject(i+1, args[i]);
}
// 执行，获取结果集
rs = ps.executeQuery();
// 获取结果集的元数据：ResultSetMetaData
ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
// 获取列数：通过 ResultSetMetaData 获取结果集中的列数
int columnCount = rsmd.getColumnCount();
if(rs.next()) {
Order order = new Order();
// 处理结果集一行数据中的每一列
for(int i = 0; i < columnCount; i++) {
// 获取每个列的列值：通过 ResultSet
Object columnValue = rs.getObject(i + 1);
// 通过 ResultSetMetaData
// 获取列的列名：getColumnName() - 不推荐使用
// 获取列的别名：getColumnLabel() - 没有起别名就是列名，推荐使用
// String columnName = rsmd.getColumnName(i + 1);// 没有用别名，用的是列名
String columnLabel = rsmd.getColumnLabel(i + 1);
// 通过反射，将对象指定名 columnName 的属性赋值为指定的值 columnValue
Field field = Order.class.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(order,columnValue);
}
return order;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps,rs);
}
return null;
}
@Test
public void testQuery1() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
String sql = "select order_id,order_name,order_date from `order` where order_id = ?";
ps = conn.prepareStatement(sql);
ps.setObject(1,1);
rs = ps.executeQuery();
if(rs.next()) {
int id = (int) rs.getObject(1);
String name = (String) rs.getObject(2);
Date date = (Date) rs.getObject(3);
Order order = new Order(id,name,date);
System.out.println(order);
//Order [orderId=1, orderName=AA, orderDate=2010-03-04]
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps, rs);
}
}
}
package com.atguigu2.statement.crud;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.Scanner;
import org.junit.Test;
public class StatementTest {
public StatementTest() {
}
// 使用 Statement 的弊端，需要拼写 sql 语句，并且存在 SQL 注入的问题
// 如何避免出现 sql 注入：只要用 PreparedStatement (从 Statement 扩展而来) 取代 Statement
@Test
public void testLogin() {
Scanner scanner = new Scanner(System.in);
System.out.print("请输入用户名：");
String user = scanner.nextLine();
System.out.print("请输入密码：");
String password = scanner.nextLine();
String sql = "SELECT user,password FROM user_table WHERE user = '" + user + "' AND password = '" + password + "'";
User returnUser = (User)this.get(sql, User.class);
if (returnUser != null) {
System.out.println("登录成功");
} else {
System.out.println("用户名不存在或密码错误");
}
}
// 使用 Statement 实现对数据表的查询操作
public <T> T get(String sql, Class<T> clazz) {
T t = null;
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
//1.加载配置文件
InputStream is = StatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
//2.读取配置信息
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
//3.加载驱动
Class.forName(driverClass);
conn = DriverManager.getConnection(url, user, password);
st = conn.createStatement();
rs = st.executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
if (!rs.next()) {
return null;
} else {
t = clazz.newInstance();
for(int i = 0; i < columnCount; ++i) {
String columnName = rsmd.getColumnLabel(i + 1);
Object columnVal = rs.getObject(columnName);
Field field = clazz.getDeclaredField(columnName);
field.setAccessible(true);
field.set(t, columnVal);
}
Object var20 = t;
return (T) var20;
}
} catch (Exception var39) {
var39.printStackTrace();
return null;
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException var38) {
var38.printStackTrace();
}
}
if (st != null) {
try {
st.close();
} catch (SQLException var37) {
var37.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException var36) {
var36.printStackTrace();
}
}
}
}
}
package com.atguigu2.statement.crud;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;
import org.junit.Test;
import com.atguigu3.util.JDBCUtils;
import com.mysql.cj.jdbc.result.ResultSetMetaData;
/**
* 演示使用 PreparedStatement 替换 Statement，解决 SQL 注入问题
*
* 除了解决 Statement 的拼串、sql 问题之外，PreparedStatement 还有哪些好处呢？
* 1.PreparedStatement 操作 Blob 的数据，而 Statement 做不到。
* 2.PreparedStatement 可以实现更高效的批量操作。
*
*/
public class PreparedStatementTest {
@Test
public void testLogin() {
Scanner scanner = new Scanner(System.in);
System.out.print("请输入用户名：");
String user = scanner.nextLine();
System.out.print("请输入密码：");
String password = scanner.nextLine();
// SELECT user,password FROM user_table WHERE user = '1' or ' AND password = '=1 or '1' = '1'
String sql = "SELECT user,password FROM user_table WHERE user = ? and password = ?";
User returnUser = getInstance(User.class, sql,user,password);
if (returnUser != null) {
System.out.println("登录成功");
} else {
System.out.println("用户名不存在或密码错误");
}
}
/**
* 针对于不同的表的通用的查询操作，返回表中的一条记录
*
*/
public <T> T getInstance(Class<T> clazz,String sql,Object...args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
for(int i = 0; i < args.length; i++) {
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
// 获取结果集的元数据：ResultSetMetaData
ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
// 通过 ResultSetMetaData 获取结果集中的列数
int columnCount = rsmd.getColumnCount();
if(rs.next()) {
T t = clazz.newInstance();
// 处理结果集一行数据中的每一个列
for(int i = 0; i < columnCount; i++) {
// 获取列值
Object columValue = rs.getObject(i + 1);
// 获取每个列的列名
// String columnName = rsmd.getColumnName(i + 1);
// 获取每个列的别名
String columnLabel = rsmd.getColumnLabel(i + 1);
// 给 t 对象指定的 columnName 属性，赋值为 columValue：通过反射
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columValue);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps, rs);
}
return null;
}
}
package com.atguigu3.preparedstatement.crud;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import org.junit.Test;
import com.atguigu3.bean.Customer;
import com.atguigu3.bean.Order;
import com.atguigu3.util.JDBCUtils;
import com.mysql.cj.jdbc.result.ResultSetMetaData;
/**
* 使用 PreparedStatement 实现针对于不同表的通用的查询操作
*
*/
public class PreparedStatementQueryTest {
@Test
public void testGetForList() {
String sql = "select id,name,email from customers where id < ?";
List<Customer> list = getForList(Customer.class,sql,12);
list.forEach(System.out::println);
// Customer [id=1, name=汪峰, email=wf@126.com, birth=null]
// Customer [id=2, name=王菲, email=wangf@163.com, birth=null]
// Customer [id=4, name=汤唯, email=tangw@sina.com, birth=null]
// Customer [id=5, name=成龙, email=Jackey@gmai.com, birth=null]
// Customer [id=6, name=迪丽热巴, email=reba@163.com, birth=null]
// Customer [id=7, name=刘亦菲, email=liuyifei@qq.com, birth=null]
// Customer [id=8, name=陈道明, email=bdf@126.com, birth=null]
// Customer [id=10, name=周杰伦, email=zhoujl@sina.com, birth=null]
System.out.println("** ** ** ** ** ** ** ** ** ** ** ** **");
// 特殊情况，没有解决 sql 注入问题，不建议使用(和 statement 效果差不多，没有解决 sql 注入问题)
String sql1 = "select id,name,email from customers where id < 12";
List<Customer> list1 = getForList(Customer.class,sql1);
list1.forEach(System.out::println);
// Customer [id=1, name=汪峰, email=wf@126.com, birth=null]
// Customer [id=2, name=王菲, email=wangf@163.com, birth=null]
// Customer [id=4, name=汤唯, email=tangw@sina.com, birth=null]
// Customer [id=5, name=成龙, email=Jackey@gmai.com, birth=null]
// Customer [id=6, name=迪丽热巴, email=reba@163.com, birth=null]
// Customer [id=7, name=刘亦菲, email=liuyifei@qq.com, birth=null]
// Customer [id=8, name=陈道明, email=bdf@126.com, birth=null]
// Customer [id=10, name=周杰伦, email=zhoujl@sina.com, birth=null]
System.out.println("** ** ** ** ** ** ** ** ** ** ** ** **");
String sql2 = "select order_id orderId,order_name orderName from `order` where order_id < ?";
List<Order> orderList = getForList(Order.class,sql2,5);
orderList.forEach(System.out::println);
// Order [orderId=1, orderName=AA, orderDate=null]
// Order [orderId=2, orderName=DD, orderDate=null]
// Order [orderId=4, orderName=GG, orderDate=null]
System.out.println("** ** ** ** ** ** ** ** ** ** ** ** **");
// 特殊情况
String sql3 = "select order_id orderId,order_name orderName from `order`";
List<Order> orderList1 = getForList(Order.class,sql3);
orderList1.forEach(System.out::println);
// Order [orderId=1, orderName=AA, orderDate=null]
// Order [orderId=2, orderName=DD, orderDate=null]
// Order [orderId=4, orderName=GG, orderDate=null]
// Customer [id=12, name=黎明, email=LiM@126.com, birth=null]
// Order [orderId=1, orderName=AA, orderDate=null]
}
public <T> List<T> getForList(Class<T> clazz,String sql,Object... args){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
for(int i = 0; i < args.length; i++) {
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
// 获取结果集的元数据：ResultSetMetaData
ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
// 通过 ResultSetMetaData 获取结果集中的列数
int columnCount = rsmd.getColumnCount();
// 创建集合对象
ArrayList<T> list = new ArrayList<T>();
while (rs.next()) {
T t = clazz.newInstance();
// 处理结果集一行数据中的每一个列:给 t 对象指定的属性赋值
for(int i = 0; i < columnCount; i++) {
// 获取列值
Object columValue = rs.getObject(i + 1);
// 获取每个列的列名
// String columnName = rsmd.getColumnName(i + 1);
// 获取每个列的别名
String columnLabel = rsmd.getColumnLabel(i + 1);
// 给 t 对象指定的 columnName 属性，赋值为 columValue：通过反射
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columValue);
}
list.add(t);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps, rs);
}
return null;
}
@Test
public void testGetInstance() {
String sql = "select id,name,email from customers where id = ?";
Customer customer = getInstance(Customer.class,sql,12);
System.out.println(customer);
// Customer [id=12, name=黎明, email=LiM@126.com, birth=null]
String sql1 = "select order_id orderId,order_name orderName from `order` where order_id = ?";
Order order = getInstance(Order.class,sql1,1);
System.out.println(order);
// Order [orderId=1, orderName=AA, orderDate=null]
}
/**
* 针对于不同的表的通用的查询操作，返回表中的一条记录
*
*/
public <T> T getInstance(Class<T> clazz,String sql,Object...args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
for(int i = 0; i < args.length; i++) {
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
// 获取结果集的元数据：ResultSetMetaData
ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
// 通过 ResultSetMetaData 获取结果集中的列数
int columnCount = rsmd.getColumnCount();
if(rs.next()) {
T t = clazz.newInstance();
// 处理结果集一行数据中的每一个列
for(int i = 0; i < columnCount; i++) {
// 获取列值
Object columValue = rs.getObject(i + 1);
// 获取每个列的列名
// String columnName = rsmd.getColumnName(i + 1);
// 获取每个列的别名
String columnLabel = rsmd.getColumnLabel(i + 1);
// 给 t 对象指定的 columnName 属性，赋值为 columValue：通过反射
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columValue);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps, rs);
}
return null;
}
}
最后
以上就是留胡子草莓为你收集整理的jdbc 与 mysql 连接 - 增删改查的全部内容，希望文章能够帮你解决jdbc 与 mysql 连接 - 增删改查所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复