JDBC完成CRUD

package zjtool;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
/*
 * JDBC工具类的封装
 */
public class jdbctool {
	private static String driverClass;
	private static String url;
	private static String username;
	private static String password;

	static{
		InputStream in = null;
		try {
			in = new FileInputStream("src/jdbc.properties");//读取jdbc.properties文件
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Properties pro=new Properties();
		try {
			pro.load(in);//通过Properties来加载jdbc.properties文件
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 driverClass = pro.getProperty("driverClass");//获得jdbc.properties文件中的值
		 url = pro.getProperty("url");
		 username = pro.getProperty("username");
		password = pro.getProperty("password");
	}
public static void loadDriver(){
	try {
		Class.forName(driverClass);//注册驱动
	} catch (ClassNotFoundException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}
public static Connection getConnectiom () {
	Connection con = null;
	loadDriver();
	try {
		 con=(Connection) DriverManager.getConnection(url,username,password);//获得连接
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
	return con;
}
public static void release(ResultSet rs,PreparedStatement pstmt,java.sql.Connection con){
	if(rs!=null){  
		try{
			rs.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		rs=null;
	}
	if(pstmt!=null){  
		try{
			pstmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		pstmt=null;
	}
	if(con!=null){  
		try{
			con.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		con=null;
	}
}	

public static void release(PreparedStatement sta,Connection con){
	if(sta!=null){  
		try{
			sta.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		sta=null;
	}
	if(con!=null){  
		try{
			con.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		con=null;
	}
}
}

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import org.junit.Test;

import com.mysql.jdbc.Statement;

import zjtool.jdbctool;
/*
 * 使用JDBC进行增删改查操作
 */

public class test {
	private com.mysql.jdbc.Connection con;
	private PreparedStatement pst;
	private int rs;
	@Test
	public void demo1(){
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try{
			Class.forName("com.mysql.jdbc.Driver");//注册驱动
			conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/zdb","root","942686");//建立连接
			String sql="select * from category";
			pstmt=conn.prepareStatement(sql);//获得操作数据库的对象
			rs= pstmt.executeQuery();//向数据库发送执行操作
			while (rs.next()) {
				System.out.println(rs.getInt("cid")+"  "+rs.getString("cname"));
			}
		}catch(Exception e){
			e.printStackTrace(); 
		}finally{//释放资源
			if(rs!=null){  
				try{
					rs.close();
				}catch(SQLException e){
					e.printStackTrace();
				}
				rs=null;
			}
			if(pstmt!=null){  
				try{
					pstmt.close();
				}catch(SQLException e){
					e.printStackTrace();
				}
				pstmt=null;
			}
			if(conn!=null){  
				try{
					conn.close();
				}catch(SQLException e){
					e.printStackTrace();
				}
				conn=null;
			}
		}
	}
	
	@Test
	public void demo2(){//将jdbc封装
		Connection con=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try{
			con=jdbctool.getConnectiom();
			String sql="select * from category";
			pstmt=con.prepareStatement(sql);
			rs= pstmt.executeQuery();
			while (rs.next()) {
				System.out.println(rs.getInt("cid")+"  "+rs.getString("cname"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			jdbctool.release(rs, pstmt, con);
		}
		}
	@Test//向表格插入值
	public void demo3(){
		con=jdbctool.getConnectiom();
		String sql="insert into category values (null,?)";
		try {
			pst=con.prepareStatement(sql);
			pst.setString(1, "鞋靴箱包");
			rs=pst.executeUpdate();
			if (rs>0) {
				System.out.println("执行成功");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			jdbctool.release(pst, con);
		}
	}
	@Test//更新表格的值
	public void demo4(){
		con=jdbctool.getConnectiom();
		String sql="update category set cname=? where cid=?";
		try {
			pst=con.prepareStatement(sql);
			pst.setString(1, "烟酒糖茶");
			pst.setInt(2, 5);
			rs=pst.executeUpdate();
			if (rs>0) {
				System.out.println("执行成功");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			jdbctool.release(pst, con);
		}
	}
	@Test//删除表格的值
	public void demo5(){
		con=jdbctool.getConnectiom();
		String sql="delete from category where cid=?";
		try {
			pst=con.prepareStatement(sql);
			pst.setInt(1, 7);
			rs=pst.executeUpdate();
			if (rs>0) {
				System.out.println("执行成功");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			jdbctool.release(pst, con);
		}
	}
	}