JDBC的增删改查一

前期准备

mysql> create database student;
mysql> use student;
mysql> create table st(
    -> id int primary key auto_increment,
    -> name varchar(10),
    -> age int
    -> );
insert into studenttest values (null,'张三',23);................

使用JDBC的基本步骤

1. 注册驱动

DriverManager.deregisterDriver(new com.mysql.jdbc.Driver());

2. 建立连接

connection = DriverManager.getConnection("jdbc:mysql://localhost/student", "root", "123456");

3. 创建statement

statement = connection.createStatement();

4. 执行sql ，得到ResultSet

String sql = "select * from st";   resultSet = statement.executeQuery(sql );

5. 遍历结果集

6. 释放资源

 

driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost/student
name=root
password=123456

package com.ny.utils;


import java.io.FileInputStream;
import java.io.InputStream;
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 MysqlDemoUtil {
	//static关键字修饰的变量供所有实例共享
	static String driverClass = null;
	static String url = null;
	static String name = null;
	static String password = null;
	
	static {
		try{
			//创建属性配置对象
		Properties pro = new Properties();
		//使用类加载器，去读取src底下的资源文件。 后面在servlet
//		InputStream is = MysqlDemoUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
		InputStream is = new FileInputStream("jdbc.properties");
		 //导入输入流
		pro.load(is);
		
		//读取属性
		driverClass = pro.getProperty("driverClass");
		url = pro.getProperty("url");
		name = pro.getProperty("name");
		password = pro.getProperty("password");
		}catch (Exception e) {
				e.printStackTrace();
		}	
	}
	
	//获取连接对象
	
	public static Connection getCon() {
		Connection con = null;
		try {
			//加载驱动程序
			Class.forName(driverClass );
			//建立连接
			con = DriverManager.getConnection(url,name,password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return con ;
	}
		
	//这样做提高了代码的复用性  是程序变得更加灵活
	public static void closeAll(ResultSet rs,Statement st,Connection con) {
		rsClose(rs);
		stClose(st);
		conClose(con);
	}
	
	public static void closeAll(Statement st,Connection con) {
		stClose(st);
		conClose(con);
	}
	
	
	public static void rsClose(ResultSet rs) {
		try {
			if(rs != null) {
			rs.close();
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void stClose(Statement st) {
		try {
			if(st != null) {
			st.close();
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void conClose(Connection con) {

		try {
			if(con != null) {
			con.close();
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		
	}
}

package com.ny.mysql;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
import com.ny.utils.MysqlDemoUtil;

public class TestMysqlJuint {
	@Test
	//查询
	public void queryFun() {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;	
		try {
			//创建连接
			con = MysqlDemoUtil.getCon();
			//创建statement
			st = con.createStatement();
			//执行语句
			String sql = "select * from st";
			//获取查询的结果
			rs = st.executeQuery(sql);
			while(rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				int age = rs.getInt("age");				
				System.out.println(name + " " + age);
			}			
		} catch (SQLException e) {		
			e.printStackTrace();
		}finally {
			MysqlDemoUtil.closeAll(rs, st, con);			
		}
	}
	//增加
//	@Test
	public void insertFun() {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;  //方法的重载	
		try {
			//创建连接
			con = MysqlDemoUtil.getCon();
			//创建 statement
			st = con.createStatement();
			String sql = "insert into st values(null,'宋江',56)";
			//执行语句不执行
			int i = st.executeUpdate(sql);
			if(i > 0) {
				System.out.println("插入成功");
			}else {
				System.out.println("插入失败");
			}
		} catch (Exception e) {
				e.printStackTrace();
		}finally {
			//方法的重载
			MysqlDemoUtil.closeAll(st, con);
		}
	}
	
	//删除
//	@Test
	public void deleteFun() {
		Connection con = null;
		Statement st = null;
		try {
			//创建连接
			con = MysqlDemoUtil.getCon();
			//创建 statement
			st = con.createStatement();
			String sql = "delete from st where id = 5 ";
			//执行语句不执行
			int i = st.executeUpdate(sql);
			if(i > 0) {
				System.out.println("删除成功");
			}else {
				System.out.println("删除失败");
			}
		} catch (Exception e) {
				e.printStackTrace();
		}finally {
			//方法的重载
			MysqlDemoUtil.closeAll(st, con);
		}
	}
	
	@Test
	public void updateFun() {		
		Connection con = null;
		Statement st = null;		
		try {
			//创建连接
			con = MysqlDemoUtil.getCon();
			//创建 statement
			st = con.createStatement();
			String sql = "update st set name = '林冲' where id = 6";
			//执行语句不执行
			int i = st.executeUpdate(sql);
			if(i > 0) {
				System.out.println("更新成功");
			}else {
				System.out.println("更新失败");
			}
		} catch (Exception e) {
				e.printStackTrace();
		}finally {
			//方法的重载
			MysqlDemoUtil.closeAll(st, con);
		}
	}

}