Java -- JDBC 操作mysql数据库

1. Demo1

导包时 不要导具体的mysql包， 为了兼容性，导JDBC 中 sql的包既可以了。

public class Demo1 {

	/**
	 * @param args
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 * @throws Exception 
	 */
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		
		String url = "jdbc:mysql://localhost:3306/day14";
		String username = "root";
		String password = "root";
		
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		
		try{
	
			//1.加载驱动(开发推荐的方式)
			Class.forName("com.mysql.jdbc.Driver");
			
			//2.获取与数据库的链接，链接资源有限，尽量晚的创建，尽量早的释放
			conn = (com.mysql.jdbc.Connection) DriverManager.getConnection(url, username, password);
			System.out.println(conn);
			
			//3.获取用于向数据库发送sql语句的statement
			st = conn.createStatement();
			
			//4.向数据库发sql,并获取代表结果集的resultset
			String sql = "select id,name,password,email,birthday from users";
			rs = st.executeQuery(sql);
			
			//5.取出结果集的数据
			rs.afterLast();
			rs.previous();
			System.out.println("id=" + rs.getObject("id"));
			System.out.println("name=" + rs.getObject("name"));
			System.out.println("password=" + rs.getObject("password"));
			System.out.println("email=" + rs.getObject("email"));
			System.out.println("birthday=" + rs.getObject("birthday"));
			
		}finally{
		//6.关闭链接，释放资源
			if(rs!=null){
				try{
					rs.close();
				}catch (Exception e) {
					e.printStackTrace();
				}
				rs = null;
	
			}
			if(st!=null){
				try{
					st.close();
				}catch (Exception e) {
					e.printStackTrace();
				}
				
			}
			
			if(conn!=null){
				try{
					conn.close();
				}catch (Exception e) {
					e.printStackTrace();
				}
				
			}
									
		}

	}

}

ResultSet还提供了对结果集进行滚动的方法：

•next()：移动到下一行

•Previous()：移动到前一行

•absolute(int row)：移动到指定行

•beforeFirst()：移动resultSet的最前面。

•afterLast() ：移动到resultSet的最后面。

2. 增、删、改、查 示例

//使用jdbc对数据库增删改查
public class Demo4 {

	@Test
	public void insert(){
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try{
			conn = JdbcUtils.getConnection();
			st = conn.createStatement();
			String sql = "insert into users(id,name,password,email,birthday) values(4,'xxx','123','xx@sina.com',to_date('1980-09-09','YYYY-MM-DD'))";
			int num = st.executeUpdate(sql);  //update
			if(num>0){
				System.out.println("插入成功！！");
			}
			
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			JdbcUtils.release(conn, st, rs);
		}
	}
	
	@Test
	public void delete(){
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try{
			conn = JdbcUtils.getConnection();
			String sql = "delete from users where id=4";
			st = conn.createStatement();
			int num = st.executeUpdate(sql);
			if(num>0){
				System.out.println("删除成功！！");
			}
		}catch (Exception e) {
			
			
		}finally{
			JdbcUtils.release(conn, st, rs);
		}
	}
	
	@Test
	public void update(){
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try{
			conn = JdbcUtils.getConnection();
			String sql = "update users set name='wuwang',email='wuwang@sina.com' where id=3";
			st = conn.createStatement();
			int num = st.executeUpdate(sql);
			if(num>0){
				System.out.println("更新成功！！");
			}
		}catch (Exception e) {
			
			
		}finally{
			JdbcUtils.release(conn, st, rs);
		}
	}
	
	@Test
	public void find(){
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try{
			conn = JdbcUtils.getConnection();
			String sql = "select * from users where id=1";
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			if(rs.next()){
				System.out.println(rs.getString("name"));
			}
		}catch (Exception e) {
			
		}finally{
			JdbcUtils.release(conn, st, rs);
		}
	}
	
}

工具类

public class JdbcUtils {

	private static String driver = null;
	private static String url = null;
	private static String username = null;
	private static String password = null;
	
	static{
		try{
			InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
			Properties prop = new Properties();
			prop.load(in);
			
			driver = prop.getProperty("driver");
			url = prop.getProperty("url");
			username = prop.getProperty("username");
			password = prop.getProperty("password");
			
			Class.forName(driver);
			
		}catch (Exception e) {
			throw new ExceptionInInitializerError(e);
		}
	}
	
	
	public static Connection getConnection() throws SQLException{
		
		return DriverManager.getConnection(url, username,password);
	}
	
	public static void release(Connection conn,Statement st,ResultSet rs){
		
		if(rs!=null){
			try{
				rs.close();
			}catch (Exception e) {
				e.printStackTrace();
			}
			rs = null;

		}
		if(st!=null){
			try{
				st.close();
			}catch (Exception e) {
				e.printStackTrace();
			}
			
		}
		
		if(conn!=null){
			try{
				conn.close();
			}catch (Exception e) {
				e.printStackTrace();
			}
			
		}
		
	}
}

配置文件 db.properties ， 配置文件 每行后面不要有空格，propertie解析的时候会将空格也解析。

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

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

2. JavaWeb 工程 结构图