ResultSetMetaData小结 PreparedStatement小结

package com.atgaozhen.jdbc;


import java.io.IOException;
import java.net.ConnectException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.Scanner;
import java.util.Map.Entry;


import javax.naming.spi.ResolveResult;
import javax.print.attribute.standard.PresentationDirection;


import org.junit.Test;


import com.mysql.jdbc.Connection;
import com.mysql.jdbc.ResultSetMetaData;
import com.mysql.jdbc.Statement;


public class JDBCTest_1 {

/*1.先利用sql进行查询，得到结果集
* 2.利用反射创建实体类的对象：创建Student对象
* 3.获取结果即的列的别名：idCard，studentName
* 4.在获取结果集的每一列的值，结合3得到一个Map，键：列的别名，值：列的值
* {flowId：5，type：6，idcard：}
* 5.再利用反射为2的对应的属性赋值：属性即为Map的键值，值即为Map的值、
* 使用JDBC驱动程序处理元数据：
* 1.JAVA通过JDBC获取连接以后，得到一个Connection对象，可以从这个对象获得有关
* 数据库管理系统的各种信息，包括数据库中的各个表，表中的各个列，数据类型，触发器，存储过程等各方面
* 的信息。根据这些信息，JDBC可以方位一个实现不知道的数据库。
* 2.获取这些信息的方法都是在 DatabaseMetaDate类的对象上的实现的、
* 而DataBaseMetaDate对象在Connection对象上获取的 
* 
* 
* ResultSetMetaData

* what：是描述ResultSet的元数据对象。

即从中可以获取到结果集中的有多少列。列名的什么

* how：
* 1.得到ResultSetMetaData 对象：调用ResultSet的getMetaData()方法
* ResultDSetMetaData 有些好用的方法

* >int getColumnCount():

sql语句中包含那些咧

* >String getColumnLabel(int column):

获取制定的列的别名。其中索引从1开始

* -------------------------ResultSetMetaData小结-----------------------
* why：如果只有一个结果集，但不知道结果集中有多少列，列的名字都是什么
* 1)编写通用的查询方法时需要使用
* public<T>T get(Class <T> clazz,String sql,Object ...args )
* what：用于描述ResultSet的对象
* how：
*    //2.得到ResultSetMetaData 对象
ResultSetMetaData resultSetMetaData=(ResultSetMetaData) resultSet.getMetaData();
//3.创建一个Map<String,Object>对象，键：sql查询的列的别名
//值：列的值
Map<String,Object> values=new HashMap<String, Object>();
//4.处理结果集，利用ResultSetMetaData填充3对应的map对象
if(resultSet.next()){
for(int i=0;i<resultSetMetaData.getColumnCount();++i){
String columnLabel=resultSetMetaData.getColumnLabel(i+1);
Object columnValue=resultSet.getObject(i+1);
values.put(columnLabel, columnValue);
}
}
//若map不为空集，利用反射创建clazz对应的对象
if(values.size()>0)
   entity=clazz.newInstance();
//5.遍历Map对象，利用反射为Class对象的对应的属性赋值
for(Map.Entry<String,Object>entry:values.entrySet()){
String fieldName=entry.getKey();
Object value=entry.getValue();
ReflectionUtils.setFieldValue(entity, fieldName, value);
}
* 
*/
@Test
public void testResultSetMetaData(){
Connection connection=null;
PreparedStatement preparedStatement=null;
ResultSet resultSet=null;
try {
String sql="SELECT * FROM examstudents WHERE flowId=?";
connection=(Connection) new JDBCTool().getConnection();
preparedStatement =connection.prepareStatement(sql);
preparedStatement.setInt(1, 1);
resultSet=preparedStatement.executeQuery();
//1.得到ResultSetMetaData对象
ResultSetMetaData resultSetMetaData=(ResultSetMetaData) resultSet.getMetaData();
Map<String, Object> values=new HashMap<String, Object>();
while(resultSet.next()){
//2.打印每一列的列名
for(int i=0;i<resultSetMetaData.getColumnCount();++i){
String columnLabel=resultSetMetaData.getColumnLabel(i+1);
Object columnvalue=resultSet.getObject(columnLabel);
values.put(columnLabel, columnvalue);
}
}
System.out.println(values);
System.out.println("-------------------------------------------------");
Class clazz=Student.class;
Object object=clazz.newInstance();
for(Map.Entry<String,Object>entry:values.entrySet()){
String fieldName=entry.getKey();
Object fieldValue=entry.getValue();
System.out.println(fieldName+":"+fieldValue);
ReflectionUtils.setFieldValue(object, fieldName, fieldValue);
}
System.out.println("-------------------------------------------------");
System.out.println(object);
} catch (Exception e) {
// TODO: handle exception
}
}
@Test
public void testGet(){
String sql="select * from examstudents where flowid=?";
Student stu=get(Student.class,sql,1);
System.out.println(stu);
System.out.println(stu.getStudentName());
}
public<T>T get(Class <T> clazz,String sql,Object ...args ){
T entity=null;
Connection connection=null;
PreparedStatement preparedStatement=null;
ResultSet resultSet=null;
//1.得到ResultSet对象
try {
connection=(Connection) new JDBCTool().getConnection();
preparedStatement=connection.prepareStatement(sql);
for(int i=0;i<args.length;++i){
preparedStatement.setObject(i+1, args[i]);
}
resultSet=preparedStatement.executeQuery();
//2.得到ResultSetMetaData 对象
ResultSetMetaData resultSetMetaData=(ResultSetMetaData) resultSet.getMetaData();
//3.创建一个Map<String,Object>对象，键：sql查询的列的别名
//值：列的值
Map<String,Object> values=new HashMap<String, Object>();
//4.处理结果集，利用ResultSetMetaData填充3对应的map对象
if(resultSet.next()){
for(int i=0;i<resultSetMetaData.getColumnCount();++i){
String columnLabel=resultSetMetaData.getColumnLabel(i+1);
Object columnValue=resultSet.getObject(i+1);
values.put(columnLabel, columnValue);
}
}
//若map不为空集，利用反射创建clazz对应的对象
if(values.size()>0)
entity=clazz.newInstance();
//5.遍历Map对象，利用反射为Class对象的对应的属性赋值
for(Map.Entry<String,Object>entry:values.entrySet()){
String fieldName=entry.getKey();
Object value=entry.getValue();
ReflectionUtils.setFieldValue(entity, fieldName, value);
}

} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return entity;
}

public Student getStudent(String sql,Object ... args ){
Student stu=null;
Connection connection=null;
PreparedStatement preparedStatement=null;
ResultSet resultSet=null;
try {
connection=(Connection) new JDBCTool().getConnection();
preparedStatement=connection.prepareStatement(sql);
for(int i=0;i<args.length;++i){
preparedStatement.setObject(i+1, args[i]);
}
resultSet=preparedStatement.executeQuery();
if(resultSet.next()){
stu=new Student();
stu.setFlowId(resultSet.getInt(1));
stu.setType(resultSet.getInt(2));
stu.setIdCard(resultSet.getString(3));
stu.setExamCard(resultSet.getString(4));
stu.setStudentName(resultSet.getString(5));
stu.setLocation(resultSet.getString(6));
stu.setGrade(resultSet.getInt(7));
}
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return stu;
}
@Test
public void testSQLInjection() throws SQLException{
//SQL注入
//能最大的提高性能
String username="a' or password = ";
String password=" or '1'='1";
String sql="select * from atguigu_1 where username='"+username+"'and "+
"password='"+password+"'";
System.out.println(sql);
Connection connection=null;
Statement statement=null;
ResultSet resultSet=null;
try {
connection=(Connection) new JDBCTool().getConnection();
statement=(Statement) connection.createStatement();
resultSet=statement.executeQuery(sql);
if(resultSet.next()){
System.out.println("登录成功");
}else{
System.out.println("用户名和密码不匹配或者用户名不存在");
}
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
new JDBCTool().CloseFunction(connection, statement, resultSet);
}
}
public void addNewStudent2(Student student) throws SQLException{
String sql="insert into examstudents values(?,?,?,?,?,?,?)";
new JDBCTool().update(sql,
student.getFlowId(),
student.getType(),
student.getIdCard(),
student.getExamCard(),
student.getStudentName(),
student.getLocation(),
student.getGrade());
}

/*可以防止sql注入攻击：
* 1.sql注入是利用某些系统没有对用户输入的数据进行充分的检查，而在用户输入数据中注入非法的
* sql语句段或者命令，从而利用系统的sql引擎完成恶意行为的做法
* 2.对于java而言，要防范sql注入，只要用PreparedStatement取代Statement就可以了
* ----------------------PreparedStatement小结-----------------------
*  1.why:
*  1)使用Statement需要进行拼写sql语句，很辛苦，而且容易出错
*  2)使用Statement可以发生sql注入
*  sql注入：
*  sql注入是利用某些系统没有对用户输入的数据进行充分的检查
*  而在用户输入数据中注入非法的sql语句段或命令，从而利用系统的sql引擎

*  完成恶意行为的做法。

*  String username="a' or password = ";
String password=" or '1'='1";
String sql="select * from atguigu_1 where username='"+username+"'and "+
"password='"+password+"'";
* what：
* 1）是Statement的子接口
* 2）可以穿入占位符的sql语句，并且提供补充占位符变量的方法
* HOW:
* String sql="INSERT INTO atguigu_1 VALUES(?,?,?)";
* 
   preparedStatement=connection.prepareStatement(sql);
   
   preparedStatement.setInt(1, 6);
   preparedStatement.setString(2, "高二");
   preparedStatement.setString(3, "password");
   
   preparedStatement.executeUpdate();
*/

@Test
  public void testPreparedStatement() throws SQLException{
 Connection connection=null;
 PreparedStatement preparedStatement=null;
 try {
connection=(Connection) new JDBCTool().getConnection();
//1.准备sql语句
   String sql="INSERT INTO atguigu_1 VALUES(?,?,?)";
   //2.获得prepareStatement
   preparedStatement=connection.prepareStatement(sql);
   //3.用set方法设置值
   preparedStatement.setInt(1, 6);
   preparedStatement.setString(2, "高二");
   preparedStatement.setString(3, "password");
   /*
    * preparedStatement.setDate(3, (java.sql.Date) new Date(new java.util.Date().getTime()));
    */
   //4.执行sql语句
   preparedStatement.executeUpdate();
 } catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}finally{
new JDBCTool().CloseFunction(connection, preparedStatement, null);
}
  }
//面向对象的思想：其中最显著的一个方面是：我们的数据表 没有把他看成一项一项的而是把它看成一个student对象的整体（封装成的student对象）
@Test
public void testGetStudent() throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException, IOException{
//1.得到查询的类型
int serchType=getSearchFromConsole();
//2.具体查询学生信息
Student student=searchStudent(serchType);
//3.打印学生信息
printStudent(student);
}
private void printStudent(Student student) {
if(student!=null){
System.out.println("flowId:"+student.getFlowId());
System.out.println("type:"+student.getType());
System.out.println("idCard:"+student.getIdCard());
System.out.println("examCard:"+student.getExamCard());
System.out.println("stduentName:"+student.getStudentName());
System.out.println("location:"+student.getLocation());
System.out.println("Grade:"+student.getGrade());
}

else{
System.out.println("查无此人");
}
}
private Student searchStudent(int searchType) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException, IOException {
   //1.根据输入的searchType，提示用户的输入信息
//1.1若searchType为1，提示：请输入身份证号，若为2提示：请输入准考证号
String sql="SELECT * FROM examstudents WHERE examCard=";
//2.根据searchType确定sql
Scanner scanner=new Scanner(System.in);
if(searchType==1){
System.out.println("请输入准考证号");
String examCard=scanner.next();
sql=sql+"'"+examCard+"'";
}else{
System.out.println("请输入身份证号");
String IdCard=scanner.next();
sql=sql+"'"+IdCard+"'";
}


//3.执行查询
Student student=getStudent(sql);
//4.若存在查询结果，把查询结果封装为一个Student对象
return student;
}
private Student getStudent(String sql) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException, IOException {
Student stu=null;
Connection connection=null;
Statement statement=null;
ResultSet resultset=null;

try {
connection=(Connection) new JDBCTool().getConnection();
statement=(Statement) connection.createStatement();
resultset=statement.executeQuery(sql);
if(resultset.next()){
stu=new Student(resultset.getInt(1)
,resultset.getInt(2),
resultset.getString(3),
resultset.getString(4),
resultset.getString(5),
resultset.getString(6),
resultset.getInt(7));
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
new JDBCTool().CloseFunction(connection, statement, resultset);
}
return stu;
}
private int getSearchFromConsole() {
System.out.print("请输入查询类型：1.用身份证查询。2.用准考证查询");
Scanner scanner=new Scanner(System.in);
int type=scanner.nextInt();
if(type!=1 && type!=2){
System.out.println("请重新输入:");
throw new RuntimeException();
}
return type;
}
@Test
public void testAddNewStudent() throws IOException, SQLException, ClassNotFoundException{
Student student=getStudentFromConsole();
addNewStudent2(student);
}

private Student getStudentFromConsole() {
Scanner scanner=new Scanner(System.in);
Student student=new Student();

System.out.print("FlowId:");
student.setFlowId(scanner.nextInt());

System.out.print("type:");
student.setType(scanner.nextInt());

System.out.print("idCard:");
student.setIdCard(scanner.next());

System.out.print("ExamCard:");
student.setExamCard(scanner.next());

System.out.print("studentName:");
student.setStudentName(scanner.next());

System.out.print("location:");
student.setLocation(scanner.next());

System.out.print("Grade:");
student.setGrade(scanner.nextInt());

return student;
}


public void addNewStudent(Student student) throws IOException, SQLException, ClassNotFoundException{
//在出入数据的时候，要拼一个sql语句，非常的麻烦
//引入：preparedstatement
/*PrepareStatement:是Statement的子接口，
* 可以传入带占位符的sql语句，并且提供了补充占位符变量的方法
* 使用PreparedStatement。创建PreparedStatement：
* String sql="insert into examstudent values(?,?,?,?,?)"
* PreparedStatement ps=conn.prepareStatement(sql)
* 调动preparedstatement的setxxx（int index，objec val）设置占位符的值index值从1开始
* 执行sql语句：excusteQuery（）或者executeUpdate()注意：执行时不需要传入sql语句
* 
*/

//1.准备一条sql语句
//只要是sql语句都要放在""中
String sql ="INSERT INTO examstudents "
+ "VALUES("+student.getFlowId()+
","+student.getType()+
",'"+student.getIdCard()+
"','"+student.getExamCard()+
"','"+student.getStudentName()+
"','"+student.getLocation()+
"',"+student.getGrade()+")";
     //调用JDBCTools类的update(sql)方法执行插入操作
System.out.println(sql);
      new JDBCTool().updatesql(sql);
}
}