JDBC连接数据库

1、基础五步

public class JDBCDemo0 {
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		
		//1.注册驱动
		Class.forName("com.mysql.jdbc.Driver");
		
		//2.创建连接对象
		Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "root");
		
		//3.编写sql语句
		String sql = "update emp set gender = '男'";
		
		//4.创建执行对象
		Statement statement = connection.createStatement();
		
		//5.执行sql语句
		int i = statement.executeUpdate(sql);
		
		System.out.println(i);
		
		//6.释放资源
		statement.close();
		connection.close();	
	}	
}

2、使用druid连接池，封装JDBCUtils

druid.properties:
jdbc.drivername=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql:///db3
jdbc.user=root
jdbc.password=root

public class JDBCUtils {
	private static DataSource dataSource;
	static {
		Properties properties = new Properties();
		InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties");
		try {
			properties.load(in);
			dataSource = DruidDataSourceFactory.createDataSource(properties);
		} catch (IOException e1) {
			e1.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public static Connection getConnection() {
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return conn;
	}
	public static void closeAll(ResultSet rs,Statement state,Connection conn) {
		try {
			if(rs!=null) {
				rs.close();
			}
			if(state!=null) {
				state.close();
			}
			if(conn!=null) {
				conn.close();
			}	
		} catch (SQLException e) {
			e.printStackTrace();
	}}
}

3、对数据库进行增删改查

public class JDBCDemo01 {
	
	public static void main(String[] args) throws SQLException, ClassNotFoundException{
		//updateEmployee();
		//insertEmployee();
		//deleteEmployee();
		//queryEmployee();
		queryEmployee2();
	}
	
	private static void queryEmployee2() {
		Connection conn = JDBCUtils.getConnection();
		Statement state=null;
		ResultSet rs=null;
		try {
			state = conn.createStatement();
			rs = state.executeQuery("select * from emp");
			ArrayList<Employee> arrayList = new ArrayList<Employee>();
			while(rs.next()) {
				Employee emp = new Employee(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getDate(5));
				arrayList.add(emp);
			}
			System.out.println(arrayList);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeAll(rs, state, conn);
		}
	}
	
	private static void queryEmployee() {
		Connection conn = JDBCUtils.getConnection();
		Statement state=null;
		ResultSet rs=null;
		try {
			state = conn.createStatement();
			rs = state.executeQuery("select * from emp");
			while(rs.next()) {
				int id = rs.getInt(1);
				String name = rs.getString(2);
				String gender = rs.getString(3);
				Date date = rs.getDate(5);
				System.out.println(id+"\t"+name+"\t"+gender+"\t"+date);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeAll(rs, state, conn);
		}
	}

	private static void deleteEmployee() {
		Connection conn = JDBCUtils.getConnection();
		String sql = "delete from dept where id = 5";
		Statement state=null;
		try {
			state = conn.createStatement();
			int i = state.executeUpdate(sql);
			System.out.println(i);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtils.closeAll(null, state, conn);
		}
	}

	private static void insertEmployee() {
		Connection conn = null;
		Statement state = null;
		try {
			conn = JDBCUtils.getConnection();
		    String sql = "insert into dept values(5,'传销部')";
			state = conn.createStatement();//4.执行对象
			int a = state.executeUpdate(sql);//5.执行sql
			System.out.println(a); 	
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeAll(null, state, conn);
		}
	}

	public static void updateEmployee() {
		Connection conn = JDBCUtils.getConnection();
		String sql = "update emp set gender = '女'";
		Statement state = null;
		try {
			state = conn.createStatement();
			int i = state.executeUpdate(sql);
			System.out.println(i);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeAll(null, state, conn);
		}			
	}
}