JDBC课堂练习

练习一:在控制台中插入一个数据到数据库里：

package Exercise;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Scanner;

import org.junit.Test;

import JDBCutil.JDBCconn;
public class Exercise1 {
public int Update(String sql,Object...args){
	Connection conn=null;
	PreparedStatement ps=null;
	try {
		Scanner sc = new Scanner(System.in);
		//1.获取数据库的链接
		conn = JDBCconn.getConn();
		//2.预编译sql语句,返回PreparedStatement的实例 
		ps = conn.prepareStatement(sql);
		//3.填充占位符
		for(int j =0;j<args.length;j++) {
			ps.setObject(j+1, args[j]);
		}
		//4.执行
		/*    ps.execute()
		 *    如果执行的是查询操作，有返回结果，则此方法返回true
		 *    如果执行的是增删改操作，没有返回结果，则次方法放回falase
		 * */
//		ps.execute();
		//此时用：ps.executeUpdate()无参数的,不要插入有参数的,那个是给statement用的
		//executeUpdate()返回一个int类型的变量,若此变量大于0则操作成功,否则则失败
		return  ps.executeUpdate();
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}finally {		
		JDBCconn.closeResource(conn, ps, null);
	}
	return 0;
}
@Test 
public void test() throws Exception {
	Scanner sc = new Scanner(System.in);
	System.out.print("请输入ID：");
	int id = sc.nextInt();
	System.out.print("请输入名字：");
	String name = sc.next();
	System.out.print("请输入邮箱地址：");
	String email = sc.next();
	SimpleDateFormat s = new SimpleDateFormat("yyyy-MM-dd");
	System.out.print("请输入格式为（yyyy-MM-dd）的日期：");
	Date data = s.parse(sc.next());
	String sql = "insert into customers(id,name,email,birth) values(?,?,?,?) ";
	int update = Update(sql,id,name,email,data);
	if(update>0) {
		System.out.println("操作成功");
	}else {
		System.out.println("操作失败");
	}
}
}

练习二:通过输入身份证号或者准考证号查询考生信息

package Exercise;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;
import java.util.Scanner;
import Test.*;
import org.junit.Test;

import Test.ExamStudentTble;

import JDBCutil.JDBCconn;

public class Exercise2 {

	public <E> E query(Class<E> clazz, String sql, Object... args) throws Exception {
		Connection conn = JDBCconn.getConn();
		PreparedStatement ps = conn.prepareStatement(sql);
		for (int i = 0; i < args.length; i++) {
			ps.setObject(i + 1, args[i]);
		}
		ResultSet rc = ps.executeQuery();
		ResultSetMetaData data = rc.getMetaData();
		int count = data.getColumnCount();
		if (rc.next()) {
			E e = clazz.newInstance();
			for (int i = 0; i < count; i++) {
				Object columnValue = rc.getObject(i + 1);
				String columnLabel = data.getColumnLabel(i + 1);
				Field field = clazz.getDeclaredField(columnLabel);
				field.setAccessible(true);
				field.set(e, columnValue);
			}
			return e;
		}
		return null;
	}
	@Test
	public void test1() {
		Scanner sc = new Scanner(System.in);
		System.out.println("请选择您要输入的类型");
		System.out.println("a身份证号：");
		System.out.println("b准考证号：");
		String stu1 = sc.next();
		if("a".equals(stu1)) {
			System.out.print("请输入身份证号:");
			String sql = "Select * from examstudent where IDcard= ?";
			ExamStudentTble query=null;
			try {
				query = query(ExamStudentTble.class, sql, sc.next());
			} catch (Exception e) {
				System.out.println("输入错误");
			}
			if(query!=null) {
				System.out.println(query);
			}else {
				System.out.println("查无此人");
			}
		}else if("b".equals(stu1)) {
			System.out.print("请输入准考证号:");
			String sql = "Select * from examstudent where ExamCard= ?";
			ExamStudentTble query=null;
			try {
				query = query(ExamStudentTble.class, sql, sc.next());
			} catch (Exception e) {
				System.out.println("输入错误");
			}
			if(query!=null) {
				System.out.println(query);
			}else {
				System.out.println("查无此人");
			}
		}else {
		   System.out.println("输入错误");
		}
	}//updateInfo
public int updateInfo(String sql,Object...args) {
		
		Connection conn=null;
		PreparedStatement p1=null;
		try {
			//1.首先和之前一样先获得4个基本信息，也就是获得配置文件
			InputStream is = ClassLoader.getSystemClassLoader().getSystemResourceAsStream("jdbc.properties");
			Properties p = new Properties();
			p.load(is);
			String url=p.getProperty("url");
			String user = p.getProperty("user");
			String psw = p.getProperty("password");
			String Diver = p.getProperty("driverClass");
			//2.注册驱动
			Class.forName(Diver);
			//3.获取链接
			conn = DriverManager.getConnection(url, user, psw);
			//4.获取PreparedStatement 预准备编译
			p1 = conn.prepareStatement(sql);
			//接下来具体的增删改操作循环，此时你有多少个参数就执行多少次，所以是args.length
			for(int i =0 ;i<args.length;i++) {
				//注意前面的参数是对应数据库中的哪个字段，而索引是从1开始的，所以是i+1，而后面的是java中的数组所以是i
				p1.setObject(i+1,args[i]);
			}
			//执行
			return p1.executeUpdate();
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			//关闭资源
		}finally {
			try {
				if(p1!=null)
				p1.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();
			}
		}
		return 0;
		
		
	}
	@Test
	public void test2() {
		Scanner sc = new Scanner(System.in);
		String sql = "delete from examstudent where FlowID =?";
//		String sql = "insert into examstudent values(?,?,?,?,?,?,?)";
		System.out.print("请输出要删除的考试的流水号：");
		int i = updateInfo(sql, sc.nextInt());
//		int i = updateInfo(sql, 7,4,"123456","1234547879","111","123","95");
		if(i>0) {
			System.out.println("删除成功");
		}else {
			System.out.println("查无此人");
		}
	}
}