使用JDBC完成CRUD操作

增删改查都要用到数据库连接对象，都要释放资源，数据库连接和资源的释放可写在工具类中

配置文件db.properties

driver=com.mysql.jdbc.Driver 
url=jdbc:mysql://localhost:3306/test
username=root
password=root

#driver=oracle.jdbc.driver.OracleDriver
#url=jdbc:oracle:thin:@localhost:1521:orcl
#username=system
#password=itcast

工具类

package cn.itcast.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JdbcUtils {
	
	private static Properties config = new Properties();
	static{
		try {
			config.load(JdbcUtils.class.getClassLoader().getResourceAsStream(\"db.properties\"));
			Class.forName(config.getProperty(\"driver\"));
		} catch (Exception e) {
			throw new ExceptionInInitializerError(e);
		}
	}

	public static Connection getConnection() throws SQLException{
		return DriverManager.getConnection(config.getProperty(\"url\"), config.getProperty(\"username\"), config.getProperty(\"password\"));
	}
	
	
	public static void release(Connection conn,Statement st,ResultSet rs){
		
		if(rs!=null){
			try{
				rs.close();   //throw new 
			}catch (Exception e) {
				e.printStackTrace();
			}
			rs = null;
		}
		if(st!=null){
			try{
				st.close();
			}catch (Exception e) {
				e.printStackTrace();
			}
			st = null;
		}
		if(conn!=null){
			try{
				conn.close();
			}catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		
	}
}	

增删改查操作

package cn.itcast.demo;


import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.junit.Test;
import cn.itcast.domain.User;
import cn.itcast.utils.JdbcUtils;

public class Demo3 {

	@Test
	public void insert() throws SQLException{
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try{
			conn = JdbcUtils.getConnection();  //oracle
			st = conn.createStatement();
			String sql = \"insert into users(id,name,password,email) values(4,\'eee\',\'123\',\'ee@sina.com\')\";
			int num = st.executeUpdate(sql);
			if(num>0){
				System.out.println(\"插入成功！！！\");
			}
		}finally{
			JdbcUtils.release(conn, st, rs);
		}
	}
	
	@Test
	public void update() throws SQLException{
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try{
			conn = JdbcUtils.getConnection();
			st = conn.createStatement();
			String sql = \"update users set name=\'fff\' where id=\'4\'\";
			int num = st.executeUpdate(sql);
			if(num>0){
				System.out.println(\"更新成功！！\");
			}
		}finally{
			JdbcUtils.release(conn, st, rs);
		}
	}
	
	@Test
	public void delete() throws SQLException{
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try{
			conn = JdbcUtils.getConnection();
			st = conn.createStatement();
			String sql = \"delete from users where id=4\";
			int num = st.executeUpdate(sql);
			if(num>0){
				System.out.println(\"删除成功！！\");
			}
		}finally{
			JdbcUtils.release(conn, st, rs);
		}
	}
	
	public void find() throws SQLException{
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try{
			conn = JdbcUtils.getConnection();
			st = conn.createStatement();
			String sql = \"select id,name,password,email,birthday from users where id=1\";
			rs  = st.executeQuery(sql);
			User user = null;
			if(rs.next()){
				user = new User();
				user.setId(rs.getInt(\"id\"));
				user.setName(rs.getString(\"name\"));
				user.setPassword(rs.getString(\"password\"));
				user.setEmail(rs.getString(\"email\"));
				user.setBirthday(rs.getDate(\"birthday\"));
			}
			System.out.println(user);
		}finally{
			JdbcUtils.release(conn, st, rs);
		}
	}
	
	@Test
	public void getAll() throws SQLException{
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try{
			conn = JdbcUtils.getConnection();
			st = conn.createStatement();
			String sql = \"select id,name,password,email,birthday from users\";
			rs  = st.executeQuery(sql);
			List list = new ArrayList();
			while(rs.next()){
				User user = new User();
				user.setId(rs.getInt(\"id\"));
				user.setName(rs.getString(\"name\"));
				user.setPassword(rs.getString(\"password\"));
				user.setEmail(rs.getString(\"email\"));
				user.setBirthday(rs.getDate(\"birthday\"));
				list.add(user);
			}
			
			System.out.println(list);
		}finally{
			JdbcUtils.release(conn, st, rs);
		}
	}
	
}