5.CRUD操作

1.增加

package cn.edu.hbue.wmp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class GeneralJDBCInsert {
	
	public static void main(String args[]){
		
		
		Student stu = new Student(14,"小汤","18462@edu","2000-4-1");
		String sql = "insert into student values(?,?,?,?)";
		isnert(sql,stu);
		
	}
	
	public static void isnert(String sql,Student stu){  //插入一条学生记录
		
		PreparedStatement ps = null;
		Connection connection = null;
		
		
		//1.获取连接
		connection = JDBCUtil.getConn();
		
		try {
			//2.预编译sql
			ps = connection.prepareStatement(sql);
			

			
			//3.填充占位符
			ps.setInt(1, stu.getId());
			ps.setString(2, stu.getName());
			ps.setString(3, stu.getEmail());
			ps.setString(4, stu.getBirth());
			
			
			//4.执行操作
			ps.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			
			//5.关闭资源
			JDBCUtil.closeConn(ps, connection);
		}
		
		
	}

}

2.删除

package cn.edu.hbue.wmp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class GeneralJDBCUpdate {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		
		String sql = "delete from student where id = ?";
		update(sql,11);

	}
	
	//要传入sql语句和不确定的占位符
	public static void update(String sql,Object ... args){
		
		Connection connection = null;
		PreparedStatement ps = null;
		//1.获取连接
		connection = JDBCUtil.getConn();
		
		try {
			//2.预编译sql语句
			ps = connection.prepareStatement(sql);
			
			//3.填充占位符
			for(int i = 0;i < args.length;i++){
				ps.setObject(i + 1, args[i]);
			}
			
			//4.执行sql语句
			ps.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			
			//5.关闭资源
			JDBCUtil.closeConn(ps,connection);
			
		}
	
	}

}

3.修改

package cn.edu.hbue.wmp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class GeneralJDBCAlter {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		
		String sql = "update student set name = ? where id = ?";
		alter(sql,"汤汤",14);
		
	}

	
	public static void alter(String sql,Object...args)
	{
		
		PreparedStatement ps = null;
		Connection connection = null;
		
		
		//1.获取连接
		connection = JDBCUtil.getConn();
		
		try {
			//2.预编译sql语句
			ps = connection.prepareStatement(sql);
			
			//3.填充占位符
			for(int i = 0;i<args.length;i++){
				
				ps.setObject(i+1, args[i]);
			}
			
			
			//4.执行操作
			ps.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			
			//5.关闭资源
			JDBCUtil.closeConn(ps, connection);
		}
		
		
		
	}
}

4.查询(最难)

在查询中如果数据库字段名和java类中属性名不一致，就在sql语句中起别名，然后在后面操作中
// 不获取列名获取列别名，不用rsmd.getColumnName(i+1)（获取列名)
String columnName = rsmd.getColumnLabel(i + 1); // 最好不管有没有不同都用这个

package cn.edu.hbue.wmp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.mysql.cj.jdbc.result.ResultSetMetaData;
import com.mysql.cj.result.Field;

public class GeneralJDBCQuery {

	public static void main(String[] args) {
	
		String sql = "select id,name,email from student where id = ?";
		Student stu = query(sql,12);
		System.out.println(stu);
		
}
	
	public static Student query(String sql,Object...args){
		
		Connection connection = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		
		//1获取连接
		connection = JDBCUtil.getConn();
		
		
	
		
		
			try {

				//2预编译SQL语句
				ps = connection.prepareStatement(sql);
				
				
				// 3.填充占位符
				for(int i = 0;i<args.length;i++){
					ps.setObject(i+1, args[i]);
				}
				

				//4.执行executeQuery(),得到结果集：ResultSet
				rs = ps.executeQuery();
				

				// 5.得到结果集的元数据：ResultSetMetaData
				ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
				

				// 6.1通过ResultSetMetaData得到columnCount,columnLabel；通过ResultSet得到列值
				//不知道要查询多少列，列数在结果集元数据有
				int columnCount = rsmd.getColumnCount();
				
				if(rs.next()){
					
					Student stu = new Student();   //先造对象，之后通过查询到的设置其value
					
					for(int i = 0;i<columnCount;i++){
						
						Object columnVal = rs.getObject(i + 1); //获取到了值但不知道是哪一列
						
						// 获取列的别名:列的别名，使用类的属性名充当
						String columnName = rsmd.getColumnLabel(i + 1); 
						
						// 6.2使用反射，给对象的相应属性赋值
						//将columnVal名字的属性赋值columnName
						java.lang.reflect.Field field = Student.class.getDeclaredField(columnName);
						field.setAccessible(true);
						field.set(stu, columnVal);
						
						
					}
					
					return stu;
				}
			} catch (NoSuchFieldException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (SecurityException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IllegalArgumentException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				
				JDBCUtil.closeConn(rs, ps, connection);
				
			}
	
		return null;
		
	}

}