day23 JDBC（Java Database Connection）连接 与 通配符与插入返回主键

JDBC配置connector的jar包

1.项目下新建lib文件夹
2.将mysql-connector-java-版本号.jar复制到lib目录下
3.右键项目名，选择Properties选项
4.点击Add JARS...，选中刚复制的jar包后，点击Apply=》OK

连接数据库

连接的过程

1）加载驱动
2）创建连接对象
3）创建执行对象
4）执行sql语句
5）处理返回结果
6）关闭访问资源

public class JdbcDemo01{
	public static final String URL = "jdbc:mysql://localhost:3306/situ001?serverTimezone=UTC&useSSL=false";//&useSSL=false可不加，serverTimezone=UTC表示时区
	public static final String USER = "root";
	public static final String PASS = "123456";
	
	static Connection conn = null;
	static PrepareStatement ps = null;
	static ResultSet rs = null;
	
	//建立数据库连接
	public static void init(){
		try{
			//加载驱动
			Class.forName("com.mysql.cj.jdbc.Driver");
			//建立连接
			conn = DriverManager.getConnection(URL,USER,PASS);
			System.out.println("建立连接成功");
		}catch (ClassNotFoundException  e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	//查询
	public static void query(String sql){
		init();
		try{
			ps = conn.prepareStatement(sql);
			rs = ps.excuteQuery(sql);
			while(rs.next()){
				System.out.println(rs.getInt("id")+","+rs.getString("s_name"));
			}
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
			close();
		}
	}
	
	//修改表（增删改）
	public static void excute(String sql){
		init();
		try{
			ps = conn.prepareStatement(sql);
			int rows = ps.excuteUpdate(sql);
			if(rows == 0) System.out.println("没有改变表中的记录");
			System.out.println("修改成功");
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
			close();
		}
	
	//添加一条数据同时返回其主键
	public static Integer addReturnPK(String sql){
		init();
		try{
			ps = conn.prepareStatement(sql,ps.RETURN_GENERATED_KEYS);
			int rows = ps.excuteUpdate();
			if(rows == 0) return null;
			Result rs2 = ps.getGeneratedKeys();
			Integer id = null;
			while(rs2.next()){
				id = rs2.getInt(1);
			}
			return id;
		}catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally{
			close();
		}
	}
	
	//关闭连接
	public static void close(){
		try {
			if(rs != null) rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		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 main(String[] args){
		//测试查询方法
		String sql = "select*from student";
		
	}

}

查询 和 增删改方法的优化（输入参数）

  	
	public static void excute(String sql, String name, Integer age){
		init();
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			ps.setInt(2, age);
			int rows = ps.executeUpdate();
			if (rows >0) {
				System.out.println("执行成功");
			}else{
				System.out.println("没有影响记录");
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("执行失败");
		}finally {
			close();
		}
		
	}
	
 	public static void query(String sql, Integer id,String name){
		init();
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.setString(2, name);
			rs = ps.executeQuery();
			while(rs.next()){
				System.out.println(rs.getInt("id")+","+rs.getString("s_name"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			close();
		}
		
	}