有没有小学生做兼职的网站,湖南城乡建设网站,百度收录哪些网站吗,天元建设集团有限公司商业承兑所需的数据库数据要导入到自己的数据库库中
三#xff1a;使用PreparedStatement实现CRUD操作
数据库连接被用于向数据库服务器发送命令和 SQL 语句#xff0c;并接受数据库服务器返回的结果。其实一个数据库连接就是一个Socket连接。CRUD操作#xff1a;根据返回值的有无…所需的数据库数据要导入到自己的数据库库中
三使用PreparedStatement实现CRUD操作
数据库连接被用于向数据库服务器发送命令和 SQL 语句并接受数据库服务器返回的结果。其实一个数据库连接就是一个Socket连接。CRUD操作根据返回值的有无可分为两类 删除添加修改查询 在 java.sql 包中有 3 个接口分别定义了对数据库的调用的不同方式 Statement用于执行静态 SQL 语句并返回它所生成结果的对象。PrepatedStatementSQL 语句被预编译并存储在此对象中可以使用此对象多次高效地执行该语句。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;}Overridepublic 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 {Testpublic 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, ClassT 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(i1);// 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 usera 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类型booleanBITbyteTINYINTshortSMALLINTintINTEGERlongBIGINTStringCHAR,VARCHAR,LONGVARCHARbyte arrayBINARY , VAR BINARYjava.sql.DateDATEjava.sql.TimeTIMEjava.sql.TimestampTIMESTAMP
4.使用PreparedStatement类完成Customers表的增删改操作
增删改操作类似下面的案例都是以增数据为例 向customers表中添加一条记录 **例此处以添加为例 Testpublic 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, GMgmail.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表中添加一条记录 Testpublic 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 {//通用的增删改操作Testpublic 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;}Overridepublic String toString() {return Customer [id id , name name , email email , birth birth ];}
}例对customers表进行单条查询操作 Testpublic 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 {Testpublic 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;}Overridepublic 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 {Testpublic 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){//获取每个列的列值:通过ResultSetObject columnValue rs.getObject(i 1);//通过ResultSetMetaData//获取列的列名getColumnName() --不推荐使用//获取列的别名getColumnLabel()
// String columnName rsmd.getColumnName(i 1);String columnLabel rsmd.getColumnLabel(i 1);//通过反射将对象指定名columnName的属性赋值为指定的值columnValueField 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;}Testpublic 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.针对不同表不同字段的查询通用操作
针对不同表不同字段的查询通用操作,此时返回表中的一条记录 Testpublic 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(ClassT 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 {Testpublic void testGetForList() {String sql select id,name,email from customers where id ?; ListCustomer list getForList(Customer.class,sql,12);//集合遍历list.forEach(System.out::println);String sql1 select order_id orderId,order_name orderName from order;ListOrder list1 getForList(Order.class,sql1);list1.forEach(System.out::println);}public T ListT getForList(ClassT 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();//创建集合对象ArrayListT list new ArrayListT();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 {Testpublic 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(ClassT 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 {Testpublic 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;}Overridepublic 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表中添加一条记录Testpublic 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:根据身份证号或者准考证号查询学生成绩Testpublic 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(ClassT 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;}//问题三删除指定的学生信息Testpublic 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(删除失败请重新操作);}}}//上一步的优化操作Testpublic 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核心技术
