JDBC写成工具类

工具类

public class jdbcUtils {
	private static String driver = "com.mysql.jdbc.Driver";
	private static String url = "jdbc:mysql://localhost:3306/product";
	private static String name = "root";
	private static String mima = "root";
static{
	
	// 因为只要注册一次,写成静态代码块,注册驱动
	try {
		Class.forName(driver);
	} catch (ClassNotFoundException e) {
		//注册驱动如果失败,就直接停了
		throw new RuntimeException("注册驱动失败");		
	}
}
public static Connection getConnection() throws Exception {

	// 连接数据库
	Connection con = DriverManager.getConnection(url, name, mima);
	//返回值
	return con;

}
public static void closeAll(Connection con,Statement st,ResultSet rs){
	
	if(con !=null){		
		try {
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}			
	}

	if(st != null){
		try {
			st.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
	}

	if(rs != null){
		try {
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
	}

	
}

---

调用

 public class Test {
    	public static void main(String[] args) {
    		//注册驱动+连接数据库
    		Connection con = null;
    		//执行语句
    		Statement st = null;
    		//操作结果集
    		ResultSet rs = null;
    		try {
    			con = jdbcUtils.getConnection();
    			st = con.createStatement();
    			rs = st.executeQuery("select *from people");
    			//读取结果集
    			while(rs.next()){
    				Object pname=rs.getObject("pname");
    				Object job =rs.getObject("job");
    				System.out.println(pname+" | "+job);
    			}
    		} catch (Exception e) {
    			e.printStackTrace();
    		}finally{
				//关闭资源
				jdbcUtils.closeAll(con, st, rs);
		}
    	}
    }

---

增删改查练习

要用prepareStatement
例子:
String sql=“select * from 表名 where cid=? and name=?”;
//先预处理
prepareStatement ps=con.prepareStatement(sql)
//把上面?的地方,替换掉(第几个"?"序号从1开始,替换的值)
ps.setObject(1, 替换的值);
ps.setObject(2, 替换的值);
//创建结果集
rs = ps.executeQuery();

public class Test02 {

	public static void main(String[] args) {
		//zeng();
		//shan();
		//gai();
		cha();
		
	}
	
	
	public static void zeng(){
		Connection con=null;
		Statement st=null;	
		try{		
			//注册驱动+连接数据库
			con =jdbcUtils.getConnection();
			//创建执行语句
			st =con.createStatement();
			//操作结果集
			int result=st.executeUpdate("insert into people(pname) values('java练习2')");		
			System.out.println(result);
		}catch(Exception e){
			e.printStackTrace();		
		}finally{
			System.out.println("关闭资源");
			jdbcUtils.closeAll(con, st,null);
		}
	}
	public static void shan(){
		Connection con=null;
		Statement st=null;
		try{
			//注册驱动
			con= jdbcUtils.getConnection();
			//执行语句
			st=con.createStatement();
			//操作
			int result =st.executeUpdate("delete from people where empno=9999");
			System.out.println(result);			
		}catch(Exception e){
			e.printStackTrace();		
		}finally{
			jdbcUtils.closeAll(con, st, null);
		}
	}
	public static void gai(){
		Connection con=null;
		Statement st=null;
		try{
			//注册驱动+连接数据库
			 con =jdbcUtils.getConnection();
			//创建执行语句
			  st= con.createStatement();
			//操作
			 int result =st.executeUpdate("update people set pname=('张三') where empno=7935");
			 System.out.println(result);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			jdbcUtils.closeAll(con, st,null);
		}
	}
	public static void cha(){
			Connection con=null;
			Statement st=null;
			ResultSet rs=null;
		try{
			//注册驱动+连接数据库
			 con=jdbcUtils.getConnection();
			//创建执行语句
			st= con.createStatement();
			//创建结果集
			rs=st.executeQuery("select *from people");
			//读取结果集
			System.out.println("工号"+" | "+"姓名"+" | "+"工作"+" | "+"mgr"+" | "+"sal"+" | "+"comm");
			while(rs.next()){
				Object empno= rs.getObject("empno");
				Object pname= rs.getObject("pname");
				Object job= rs.getObject("job");
				Object mgr= rs.getObject("mgr");
				Object sal= rs.getObject("sal");
				Object comm= rs.getObject("comm");
				
				System.out.println(empno+" | "+pname+" | "+job+" | "+mgr+" | "+sal+" | "+comm);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			jdbcUtils.closeAll(con, st, rs);
		}
	}
}

登录账号小练习

这里弃用createStatement,因为会sql注入

	//输入账号密码
	Scanner sc = new Scanner(System.in);
	System.out.println("账号");
	String zhanghao=sc.nextLine();		
	System.out.println("密码");
	String mima=sc.nextLine();		

	Connection con = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	try {
		//注册驱动+连接数据库
		con = tools.lianjie();
		
		//创建执行语句
		//prepareStatement语法,不要直接替换,写成?
		String sql="select *from people where user=? and password=?";
		ps = con.prepareStatement(sql);
		//把上面?的地方,替换掉(第几个"?"序号从1开始,替换的值)
		ps.setObject(1, zhanghao);
		ps.setObject(2, mima);
		//创建结果集
		rs = ps.executeQuery();
		//根据结果集给出结果
		if(rs.next()){
			System.out.println("登录成功");
		}else{
			System.out.println("登录失败");
		}
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
	//释放资源
	tools.closeAll(con, ps, rs);

---

用Properties配置数据库文件

好处:不需要在工具类中改动数据库信息
1.写一个配置文件

1.新建一个文件config.properties
2.写入数据库信息
	url=jdbc:mysql://localhost:3306/product
	driver=com.mysql.jdbc.Driver
    user=root
    password=root

2.修改工具类中的代码

public class tools {
	//改动1
	//用配置文件的方法,这里只要创建对象就好
	static String url=null;
	static String driver=null;
	static String user=null;
	static String password=null;
	//注册驱动
	static {
		try {
			改动2
			//创建配置文件对象
			Properties pt=new Properties();
			//读取配置文件中的内容
			pt.load(new FileInputStream("config.properties"));
			//取出对应的值
			driver= pt.getProperty("driver");
			url=pt.getProperty("url");
			user=pt.getProperty("user");
			password=pt.getProperty("password");
			
			//正式注册
			Class.forName(driver);
			
		} catch (Exception e) {
			throw new RuntimeException("驱动加载异常,请重试");
		}			
	}
	
	public static Connection lianjie() throws Exception{
		
		//创建连接
		Connection con=DriverManager.getConnection(url, user, password);
		return con;
	}
	
	public static void closeAll(Connection con,Statement st,ResultSet rs){
		//关闭资源
		if(con != null){
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(st !=null){
			try {
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(rs !=null){
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}	
	}
}