所需的数据库数据要导入到自己的数据库库中
三：使用PreparedStatement实现CRUD操作
- 数据库连接被用于向数据库服务器发送命令和 SQL 语句，并接受数据库服务器返回的结果。其实一个数据库连接就是一个Socket连接。
- CRUD操作：根据返回值的有无可分为两类
- 删除，添加，修改
- 查询
- 在 java.sql 包中有 3 个接口分别定义了对数据库的调用的不同方式：
- Statement：用于执行静态 SQL 语句并返回它所生成结果的对象。
- PrepatedStatement：SQL 语句被预编译并存储在此对象中，可以使用此对象多次高效地执行该语句。
- CallableStatement：用于执行 SQL 存储过程
1.Statement类（可略过）
示例代码
public class User {
private String user;
private String password;
public User() {
}
public User(String user, String password) {
super();
this.user = user;
this.password = password;
}
@Override
public String toString() {
return "User [user=" + user + ", password=" + password + "]";
}
public String getUser() {
return user;
}
public void setUser(String user) {
this.user = user;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
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 {
@Test
public void testLogin() {
Scanner scan = new Scanner(System.in);
System.out.print("用户名：");
String userName = scan.nextLine();
System.out.print("密 码：");
String password = scan.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 user = get(sql, User.class);
if (user != 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);
// 4.获取连接
conn = DriverManager.getConnection(url, user, password);
st = conn.createStatement();
rs = st.executeQuery(sql);
// 获取结果集的元数据
ResultSetMetaData rsmd = rs.getMetaData();
// 获取结果集的列数
int columnCount = rsmd.getColumnCount();
if (rs.next()) {
t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
// //1. 获取列的名称
// String columnName = rsmd.getColumnName(i+1);
// 1. 获取列的别名
String columnName = rsmd.getColumnLabel(i + 1);
// 2. 根据列名获取对应数据表中的数据
Object columnVal = rs.getObject(columnName);
// 3. 将数据表中得到的数据，封装进对象
Field field = clazz.getDeclaredField(columnName);
field.setAccessible(true);
field.set(t, columnVal);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭资源
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return null;
}
}
- 上面的代码有两个问题
- 问题一：存在拼串操作
在SQL语句的编写中存在拼串的操作 - 问题二：存在SQL注入问题
在用户输入数据中注入非法的 SQL 语句段或命令
(如：SELECT user, password FROM user_table WHERE user='a' OR 1 = ' AND password = ' OR '1' = '1')
- 问题一：存在拼串操作
使用PreparedStatement(从Statement扩展而来) 取代 Statement 就可以防范 SQL 注入
代码优化见8.user查询的优化
2.PreparedStatement类
-
可以通过调用
Connection对象的 preparedStatement(String sql) 方法获取PreparedStatement对象 -
PreparedStatement 接口是 Statement 的子接口，它表示一条预编译过的 SQL 语句
-
PreparedStatement 对象所代表的 SQL 语句中的参数用问号(
?)来表示，调用 PreparedStatement 对象的 setXxx() 方法来设置这些参数. setXxx() 方法有两个参数，第一个参数是要设置的 SQL 语句中的参数的索引(从 1 开始)，第二个是设置的 SQL 语句中的参数的值
3.Java与SQL对应数据类型转换表
| Java类型 | SQL类型 |
|---|---|
| boolean | BIT |
| byte | TINYINT |
| short | SMALLINT |
| int | INTEGER |
| long | BIGINT |
| String | CHAR,VARCHAR,LONGVARCHAR |
| byte array | BINARY , VAR BINARY |
| java.sql.Date | DATE |
| java.sql.Time | TIME |
| java.sql.Timestamp | TIMESTAMP |
4.使用PreparedStatement类完成Customers表的增删改操作
增删改操作类似下面的案例都是以增数据为例
向customers表中添加一条记录
**例：此处以添加为例
@Test
public void testInsert() {
Connection conn = null;
PreparedStatement ps = null;
try {
//1.读取配置文件中的配置信息
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);
//4.预编译sql语句，返回PreparedStatement的实例
String sql = "insert into customers(name,email,birth)values(?,?,?)";//?:占位符
ps = conn.prepareStatement(sql);
//5.填充占位符
ps.setString(1, "邓紫棋");
ps.setString(2, "GM@gmail.com");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date date = sdf.parse("2001-09-15");
ps.setDate(3, new Date(date.getTime()));
//6.执行SQL操作
ps.execute();
} catch (Exception e) {
e.printStackTrace();
}finally {//7.资源关闭
try {
if(ps != null) {
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn != null) {
conn.close();
}
} catch(Exception e) {
e.printStackTrace();
}
}
}
5.使用PreparedStatement类完成Customers表的增删改操作（上一步的优化）
将通用的连接与关闭资源写成一个工具类
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
//操作数据库的工具类
public class JDBC_Utils {
//获取数据库连接
public static Connection getConnection() throws Exception {
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");
Class.forName(driverClass);
Connection conn = DriverManager.getConnection(url,user,password);
return conn;
}
//关闭连接与Statement资源的操作
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(Exception e) {
e.printStackTrace();
}
}
}
上面的类命名时最好不要带下滑线，对测试向customers表中添加一条记录
@Test
public void testUpdate(){
Connection conn = null;
PreparedStatement ps = null;
try {
//1.连接数据库
conn = JDBC_Utils.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.执行SQL语言
ps.execute();
} catch (Exception e) {
e.printStackTrace();
}finally {
//5.资源关闭
JDBC_Utils.closeResource(conn, ps);
}
}
6.使用PreparedStatement类完成Customers表的增删改通用操作
public class UpdateTest {
//通用的增删改操作
@Test
public void testCommonUpdate() {
// String sql = "delete from customers where id = ?";
// update(sql,3);
String sql = "update `order` set order_name = ? where order_id = ?";//当表名与关键字重合，可以用` `在SQL语句中加以区分
update(sql,"DD","2");
}
public void update(String sql,Object ...args) {
Connection conn = null;
PreparedStatement ps = null;
try {
//1.获取数据库的连接
conn = JDBC_Utils.getConnection();
//2.预编译SQL语句
ps = conn.prepareStatement(sql);
//3.填充占位符（可变形参长度与SQL占位符数量一致）
for(int i = 0;i < args.length;i++) {
ps.setObject(i + 1,args[i]);//小心参数声明错误（从1开始）
}
//4.执行
ps.execute();
} catch (Exception e) {
e.printStackTrace();
}finally {
//5.关闭资源
JDBC_Utils.closeResource(conn, ps);
}
}
}
7.使用PreparedStatement类完成Customers表查询操作(单条数据)
import java.sql.Date;
/**
* ORM编程思想（object relational mapping）对象关系映射
* 一个数据表对应一个Java类
* 表中的一条记录对应Java类的一个对象
* 表中的一个字段对应Java类的一个属性
*/
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 + "]";
}
}
例：对customers表进行单条查询操作
@Test
public void testQuery1() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
conn = JDBC_Utils.getConnection();
String sql = "select id,name,email,birth from customers where id = ?";
ps = conn.prepareStatement(sql);
ps.setObject(1, 1);
//执行并返回结果集
resultSet = ps.executeQuery();
//处理结果集
if(resultSet.next()) {//next()：判断结果集下一条是否有数据；有，true并指针下移；没有，false,指针不下移；
//获取当条数据的各个字段值
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 {
//关闭资源
JDBC_Utils.closeResource(conn, ps,resultSet);
}
}
8.使用PreparedStatement类完成Customers表查询通用操作(单条数据)
针对表的字段名与类的属性名不同的情况（如：针对Order表的通用的查询操作）
- 1.必须在声明SQL时，使用类的属性名来命名表中字段的别名
- 2.使用
ResultSetMetaData时，需要使用getColumnLabel()来替换getColumnName()获得列的列名 - 3.如果sql中没有给字段起别名时，查询的就是列的列名
例：针对Customers表的查询操作
public class CustomersQuery {
@Test
public void testQueryForCustomers() {
// String sql = "select id,name,email,birth from customers where id = ?";
// Customer customer = queryForCustomers(sql,13);
// System.out.println(customer);
String sql = "select id,name,email,birth from customers where name = ?";
Customer customer = queryForCustomers(sql,"周杰伦");
System.out.println(customer);
}
/*
*针对customers表的通用操作
*/
public Customer queryForCustomers(String sql,Object ...args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBC_Utils.getConnection();
ps = conn.prepareStatement(sql);
for(int i = 0;i < args.length;i++) {
ps.setObject(i + 1, args[i]);
}
rs = ps.executeQuery();
//获取结果集的元数据
ResultSetMetaData rsmd = rs.getMetaData();
//通过ResultSetMetaData获取结果集中的列数
int columnCount = rsmd.getColumnCount();
if(rs.next()) {
Customer cust = new Customer();
for(int i = 0;i < columnCount;i++) {
//获取列值：结果集
Object columnvalue = rs.getObject(i +1);
//获取每个列的列名：结果集元数据
// String columnName = rsmd.getColumnName(i + 1);
//获取每个列的别名（无别名就获取表的列名）：结果集元数据
String columnLabel = rsmd.getColumnLabel(i + 1);
//给cust指定columnName属性赋值为columnvalue，通过反射
Field field = Customer.class.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(cust, columnvalue);
}
return cust;
}
}catch (Exception e) {
e.printStackTrace();
}finally {
JDBC_Utils.closeResource(conn, ps, rs);
}
return null;
}
例：针对Order表的通用的查询操作
order类
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 + "]";
}
}
查询操作
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import org.junit.Test;
import com.jdbc.util.JDBC_Utils;
/**
* 针对于Order表的通用的查询操作
*/
public class OrderForQuery {
@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表的查询操作
*/
public Order orderForQuery(String sql,Object...args){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBC_Utils.getConnection();
ps = conn.prepareStatement(sql);
for(int i = 0;i < args.length;i++){
ps.setObject(i + 1, args[i]);
}
//执行，获取结果集
rs = ps.executeQuery();
//获取结果集的元数据
ResultSetMetaData rsmd = rs.getMetaData();
//获取列数
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{
JDBC_Utils.closeResource(conn, ps, rs);
}
return null;
}
@Test
public void testQuery1(){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBC_Utils.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);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBC_Utils.closeResource(conn, ps, rs);
}
}
}
9.针对不同表不同字段的查询通用操作
针对不同表不同字段的查询通用操作,此时返回表中的一条记录
@Test
public void test1() {
String sql = "select id,name,email from customers where id = ?";
Customer customer = getInstance(Customer.class,sql,12);
System.out.println(customer);
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);
}
public <T> T getInstance(Class<T> clazz,String sql,Object ...args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBC_Utils.getConnection();
ps = conn.prepareStatement(sql);
for(int i = 0;i < args.length;i++) {
ps.setObject(i + 1, args[i]);
}
rs = ps.executeQuery();
//获取结果集的元数据
ResultSetMetaData rsmd = rs.getMetaData();
//通过ResultSetMetaData获取结果集中的列数
int columnCount = rsmd.getColumnCount();
if(rs.next()) {
T t = clazz.newInstance();
for(int i = 0;i < columnCount;i++) {
//获取列值：结果集
Object columnvalue = rs.getObject(i +1);
//获取每个列的列名：结果集元数据
// String columnName = rsmd.getColumnName(i + 1);
//获取每个列的别名（无别名就获取表的列名）：结果集元数据
String columnLabel = rsmd.getColumnLabel(i + 1);
//给cust指定columnName属性赋值为columnvalue，通过反射
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnvalue);
}
return t;
}
}catch (Exception e) {
e.printStackTrace();
}finally {
JDBC_Utils.closeResource(conn, ps, rs);
}
return null;
}
针对不同表不同字段的查询通用操作,返回多条数据
public class PreparedStatementQeryTest {
@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);
String sql1 = "select order_id orderId,order_name orderName from `order`";
List<Order> list1 = getForList(Order.class,sql1);
list1.forEach(System.out::println);
}
public <T> List<T> getForList(Class<T> clazz,String sql,Object ...args){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBC_Utils.getConnection();
ps = conn.prepareStatement(sql);
for(int i = 0;i < args.length;i++) {
ps.setObject(i + 1, args[i]);
}
rs = ps.executeQuery();
//获取结果集的元数据
ResultSetMetaData rsmd = 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 columnvalue = rs.getObject(i +1);
//获取每个列的列名：结果集元数据
// String columnName = rsmd.getColumnName(i + 1);
//获取每个列的别名（无别名就获取表的列名）：结果集元数据
String columnLabel = rsmd.getColumnLabel(i + 1);
//给cust指定columnName属性赋值为columnvalue，通过反射
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnvalue);
}
list.add(t);
}
return list;
}catch (Exception e) {
e.printStackTrace();
}finally {
JDBC_Utils.closeResource(conn, ps, rs);
}
return null;
}
}
增删改类似
10.user查询的优化
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Scanner;
import org.junit.Test;
import com.jdbc.util.JDBC_Utils;
public class PreparedStatementTest {
@Test
public void testLogin() {
Scanner scan = new Scanner(System.in);
System.out.print("用户名：");
String userName = scan.nextLine();
System.out.print("密 码：");
String password = scan.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 user = getInstance(User.class, sql,userName,password);
if (user != null) {
System.out.println("登陆成功!");
} else {
System.out.println("用户名或密码错误！");
}
}
//使用PreparedStatement针对不同表不同字段的查询通用操作,此时返回表中的一条记录
public <T> T getInstance(Class<T> clazz,String sql,Object ...args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBC_Utils.getConnection();
ps = conn.prepareStatement(sql);
for(int i = 0;i < args.length;i++) {
ps.setObject(i + 1, args[i]);
}
rs = ps.executeQuery();
//获取结果集的元数据
ResultSetMetaData rsmd = rs.getMetaData();
//通过ResultSetMetaData获取结果集中的列数
int columnCount = rsmd.getColumnCount();
if(rs.next()) {
T t = clazz.newInstance();
for(int i = 0;i < columnCount;i++) {
//获取列值：结果集
Object columnvalue = rs.getObject(i +1);
//获取每个列的列名：结果集元数据
// String columnName = rsmd.getColumnName(i + 1);
//获取每个列的别名（无别名就获取表的列名）：结果集元数据
String columnLabel = rsmd.getColumnLabel(i + 1);
//给cust指定columnName属性赋值为columnvalue，通过反射
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnvalue);
}
return t;
}
}catch (Exception e) {
e.printStackTrace();
}finally {
JDBC_Utils.closeResource(conn, ps, rs);
}
return null;
}
}
11.练习
练习一
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Scanner;
import org.junit.Test;
import com.jdbc.util.JDBC_Utils;
public class Exer1Test {
@Test
public void testInsert() {
Scanner scanner = new Scanner(System.in);
System.out.print("请输入用户名： ");
String name = scanner.next();
System.out.print("请输入邮箱： ");
String email = scanner.next();
System.out.print("请输入生日： ");
String birthday = scanner.next();
String sql = "insert into customers(name,email,birth)values(?,?,?)";
int insertCount = update(sql,name,email,birthday);
if(insertCount > 0) {
System.out.println("添加成功");
}else {
System.out.println("添加失败");
}
}
//通用的增删改操作
public int update(String sql,Object ...args) {
Connection conn = null;
PreparedStatement ps = null;
try {
//1.获取数据库的连接
conn = JDBC_Utils.getConnection();
//2.预编译SQL语句
ps = conn.prepareStatement(sql);
//3.填充占位符（可变形参长度与SQL占位符数量一致）
for(int i = 0;i < args.length;i++) {
ps.setObject(i + 1,args[i]);//小心参数声明错误（从1开始）
}
//4.执行
/**
* ps.execute()：
* false:执行的是增，删改操作，无返回值
* ture:执行的是查询操作，有返回值
*/
//方式一：
// return ps.execute();
//方式二：
return ps.executeUpdate();//返回的是影响的行数
} catch (Exception e) {
e.printStackTrace();
}finally {
//5.关闭资源
JDBC_Utils.closeResource(conn, ps);
}
return 0;
}
}
练习二
Student类
public class Student {
private int flowID;//流水号
private int type;//考试类型
private String IDCard;//身份证号
private String examCard;//准考证号
private String name;//学生姓名
private String location;//学生所在城市
private int grade;//考试成绩
public Student() {
super();
}
public Student(int flowID, int type, String iDCard, String examCard, String name, String location, int grade) {
super();
this.flowID = flowID;
this.type = type;
IDCard = iDCard;
this.examCard = examCard;
this.name = name;
this.location = location;
this.grade = grade;
}
public int getType() {
return type;
}
public void setType(int type) {
this.type = type;
}
public String getIDCard() {
return IDCard;
}
public void setIDCard(String iDCard) {
IDCard = iDCard;
}
public String getExamCard() {
return examCard;
}
public void setExamCard(String examCard) {
this.examCard = examCard;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getLocation() {
return location;
}
public void setLocation(String location) {
this.location = location;
}
public int getGrade() {
return grade;
}
public void setGrade(int grade) {
this.grade = grade;
}
public int getFlowID() {
return flowID;
}
@Override
public String toString() {
System.out.println("===============查询结果===================");
return info();
}
private String info() {
return "流水号：" + flowID + "\n四级/六级：" + type + "\n身份证号：" +
IDCard + "\n准考证号：" + examCard + "\n学生姓名：" + name + "\n区域：" + location + "\n成绩：" + grade;
}
}
三个小问题写在一起
mport java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Scanner;
import org.junit.Test;
import com.jdbc.util.JDBC_Utils;
public class Exer1Test2 {
//问题1：向examstudent表中添加一条记录
@Test
public void testInsert() {
Scanner scanner = new Scanner(System.in);
System.out.print("四级/六级：");
int type = scanner.nextInt();
System.out.print("身份证号：");
String IDCard = scanner.next();
System.out.print("准考证号：");
String examCard = scanner.next();
System.out.print("学生姓名：");
String studentName = scanner.next();
System.out.print("所在城市：");
String location = scanner.next();
System.out.print("考试成绩：");
int grade = scanner.nextInt();
String sql = "insert into examstudent(type,IDCard,examCard,studentName,location,grade)values(?,?,?,?,?,?)";
int insertCount = update(sql,type,IDCard,examCard,studentName,location,grade);
if(insertCount > 0) {
System.out.println("添加成功");
}else {
System.out.println("添加失败");
}
}
//通用的增删改操作
public int update(String sql,Object ...args) {
Connection conn = null;
PreparedStatement ps = null;
try {
//1.获取数据库的连接
conn = JDBC_Utils.getConnection();
//2.预编译SQL语句
ps = conn.prepareStatement(sql);
//3.填充占位符（可变形参长度与SQL占位符数量一致）
for(int i = 0;i < args.length;i++) {
ps.setObject(i + 1,args[i]);//小心参数声明错误（从1开始）
}
//4.执行
/**
* ps.execute()：
* false:执行的是增，删改操作，无返回值
* ture:执行的是查询操作，有返回值
*/
//方式一：
// return ps.execute();
//方式二：
return ps.executeUpdate();//返回的是影响的行数
} catch (Exception e) {
e.printStackTrace();
}finally {
//5.关闭资源
JDBC_Utils.closeResource(conn, ps);
}
return 0;
}
//问题2:根据身份证号或者准考证号查询学生成绩
@Test
public void queryWithID() {
System.out.println("请选择你要输入的查询类型：");
System.out.println("a.准考证号");
System.out.println("b.身份证号");
Scanner scanner = new Scanner(System.in);
String selection = scanner.next();
if("a".equalsIgnoreCase(selection)) {
System.out.println("请输入准考证号：");
String examCard = scanner.next();
String sql = "select FlowID flowID,Type type,IDCard,ExamCard examCard,StudentName name,Location location,Grade grade from examstudent where ExamCard = ?";
Student student = getInstance(Student.class,sql,examCard);
if(student != null) {
System.out.println(student);
}else {
System.out.println("输入准考证号有误");
}
}else if("b".equalsIgnoreCase(selection)) {
System.out.println("请输入身份证号：");
String IDCard = scanner.next();
String sql = "select FlowID flowID,Type type,IDCard,ExamCard examCard,StudentName name,Location location,Grade grade from examstudent where IDCard = ?";
Student student = getInstance(Student.class,sql,IDCard);
if(student != null) {
System.out.println(student);
}else {
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 = JDBC_Utils.getConnection();
ps = conn.prepareStatement(sql);
for(int i = 0;i < args.length;i++) {
ps.setObject(i + 1, args[i]);
}
rs = ps.executeQuery();
//获取结果集的元数据
ResultSetMetaData rsmd = rs.getMetaData();
//通过ResultSetMetaData获取结果集中的列数
int columnCount = rsmd.getColumnCount();
if(rs.next()) {
T t = clazz.newInstance();
for(int i = 0;i < columnCount;i++) {
//获取列值：结果集
Object columnvalue = rs.getObject(i +1);
//获取每个列的列名：结果集元数据
// String columnName = rsmd.getColumnName(i + 1);
//获取每个列的别名（无别名就获取表的列名）：结果集元数据
String columnLabel = rsmd.getColumnLabel(i + 1);
//给cust指定columnName属性赋值为columnvalue，通过反射
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnvalue);
}
return t;
}
}catch (Exception e) {
e.printStackTrace();
}finally {
JDBC_Utils.closeResource(conn, ps, rs);
}
return null;
}
//问题三：删除指定的学生信息
@Test
public void testDeletByExamCard() {
System.out.println("请输入学生的考号：");
Scanner scanner = new Scanner(System.in);
String examCard = scanner.next();
//查询指定准考证号的学生
String sql = "select FlowID flowID,Type type,IDCard,ExamCard examCard,StudentName name,Location location,Grade grade from examstudent where ExamCard = ?";
Student student = getInstance(Student.class,sql,examCard);
if(student == null) {
System.out.println("查无此人，请重新输入");
}else {
String sql1 = "delete from examstudent where examCard = ?";
int updateCount = update(sql1,examCard);
if(updateCount > 0) {
System.out.println("删除成功");
}else {
System.out.println("删除失败，请重新操作");
}
}
}
//上一步的优化操作
@Test
public void testdelete() {
System.out.println("请输入学生的考号：");
Scanner scanner = new Scanner(System.in);
String examCard = scanner.next();
String sql = "delete from examstudent where examCard = ?";
int updateCount = update(sql,examCard);
if(updateCount > 0) {
System.out.println("删除成功");
}else {
System.out.println("查无此人，请重新输入");
}
}
}
其中可能会出现不少的小问题，请多多包含
感谢大家的支持，关注，评论，点赞！
参考资料：尚硅谷_宋红康_JDBC核心技术
