JDBC连接数据库----增删改查

//查询操作

@Test
	public void testQuery()  { 
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet resultSet = null;
		try { 
			//连接数据库
			conn = JDBCUitl.getConnection(); 
			
			//预编译sql
			String sql="select id,name,age,score,birthday from person where id=? ";
			ps = conn.prepareStatement(sql); 
			
			ps.setObject(1, 6); 
			
			//执行操作，返回结果集
			resultSet = ps.executeQuery(); 
			
			//遍历结果集
			if(resultSet.next()) {
				int id = resultSet.getInt(1);
				String name = resultSet.getString(2);
				int age = resultSet.getInt(3);
				double score = resultSet.getDouble(4);
				Date birthday = resultSet.getDate(5);
				
				
			   //返回的结果集封装在Person类中
				Person person=new Person(id, name, age, score, birthday);
				System.out.println(person);
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUitl.closeResource(conn, ps, resultSet);
		}	
	} 

**//修改**



@Test
	public void testUPdate()  {
		
		Connection conn=null;
		PreparedStatement ps=null;
		try {  
			//1.获取数据库链接
			conn = JDBCUitl.getConnection();
			
			//2.预编译sql，获取PrepratedStatement实例 
			    String sql="Update student set stu_name=? where stu_id=?";
			     PreparedStatement ps1 = conn.prepareStatement(sql);
			
			//3.填充占位符  
			    ps1.setString(1, "柯震东");
			    ps1.setString(2, "1004");
			    
			//4.执行 
			    ps1.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			
			//5.资源的关闭 
		    JDBCUitl.closeResource(conn, ps);
		}	
	} 

//插入，删除
//向person表中添加一条数据，删除一行数据

@Test
	public void testInsert() {
		     
		    //1.读取配置文件中的四个基本信息
			
			try {
				
				InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
				
				
				 Properties pro = new Properties();
				 pro.load(is); 
				 
				 String user = pro.getProperty("user");
				 String password = pro.getProperty("password");
				 String url = pro.getProperty("url");
				 String driverClass = pro.getProperty("driverClass");
				 
				 //2.加载驱动
				 Class.forName(driverClass);
				 
				//3.获取连接
					Connection conn=null;
					PreparedStatement ps=null;
				 
				 conn = DriverManager.getConnection(url, user, password);
				 
				// 4.预编译sql语句
				 
				 //插入数据
				// String sql="insert into person(id,name,age,score,birthday) values(?,?,?,?,?)";//?占位符
				 String sql="insert into student values(?,?,?,?,?)";//?占位符
				 
				//删除数据
				// String sql="delete from student where stu_id=?";
				 
				 PreparedStatement ps1 = conn.prepareStatement(sql); 
				 
				 //5.填充占位符 
				 
				// ps1.setString(1, "1003");
			
				 
				 ps1.setString(1, "1003");
				 ps1.setString(2,"范冰冰");
				 ps1.setString(3,"女");
				 ps1.setInt(4, 100);
				 ps1.setString(5,"2班"); 
		 
			
				 
				 //6.执行操作
				 ps1.execute();
			} catch (Exception e) {
				e.printStackTrace();
			}finally {
				 //7.资源的关闭
				
						try { 
							
							InputStream ps = null;
							if(ps!=null)
						ps.close();
						} catch (Exception e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
						
						
						try { 
							InputStream conn = null;
							if(conn!=null)
							conn.close();
						} catch (Exception e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}					
				}	

/**
 * 操作数据库的工具类
 * @author ZhaoJiu
 *
 */ 

```java
public class JDBCUitl { 
	/**
	 * 获取数据库的链接
	 * @return
	 * @throws Exception
	 */
	
  public static Connection getConnection() throws Exception {
	  InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
		
		
		 Properties pro = new Properties(); 
		 pro.load(is); 
		 
		 String user = pro.getProperty("user");
		 String password = pro.getProperty("password");
		 String url = pro.getProperty("url");
		 String driverClass = pro.getProperty("driverClass");
		 
		 //2.加载驱动
		 Class.forName(driverClass);
		 
		//3.获取连接
			Connection conn=null;
			PreparedStatement ps=null; 
			
			return conn = DriverManager.getConnection(url, user, password);

  }  
  
  
  /**
   * 关闭资源
   * @param conn
   * @param ps
   */
         public static void closeResource(Connection conn,PreparedStatement ps) {
        	  
        	 try { 
					
				
					if(ps!=null)
				      ps.close();
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
				
				try { 
					
					if(conn!=null)
					  conn.close();
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
         } 
         
         public static void closeResource(Connection conn,PreparedStatement ps,ResultSet rs) {
        	  
        	 try { 
					
 				
					if(ps!=null)
				      ps.close();
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
				
				try { 
					
					if(conn!=null)
					  conn.close();
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}   
				
				try { 
					
					if(rs!=null)
					  rs.close();
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
         }