通过JDBC连接MySql数据库进行操作

JDBC连接MySql数据库

1.导入相应的jar包并配置到系统中

jdbc传统方式: mysql-connector-java
jdbc连接池:commons–dbcp和commons-pool(创建连接是一个复杂的过程，会降低
系统的性能,连接池可以高效,安全的复用性)。

dao:接口和实现(impl)
entity:实体类(主要用于封装对象)
test:测试类
util:工具类(在src下创建db.properties的文件用于连接数据库工具类,方便直接使用不需要频繁通过反射实例化驱动)。

db.properties文件属性(乱码可加?characterEncoding=utf-8)

#连接mysql相关数据
mysql_driver=com.mysql.jdbc.Driver
mysql_url=jdbc:mysql://localhost:3306/jsd?characterEncoding=utf-8
mysql_user=root
mysql_pw=1234

#连接池相关参数
#初始连接数
dataSource.initailSize=10
#最大空闲数
dataSource.maxIdle=5
#最小空闲数
dataSource.minIdle=2
#最大连接数
dataSource.maxActive=8
#超时时间1分钟
dataSource.maxWait=60000

config.properties文件

UserDao=com.ylb.dao.impl.UserDaoImpl

util工具类

//传统方式
package com.ylb.util;

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

/**
 * 工具类
 *  1.读文件取数据
 * 2.封装连接、关闭的方法
 * 
 * */
public class DBUtil {
	//定义四个变量接收数据
	private static String driver=null;
	private static String url=null;
	private static String user=null;
	private static String password=null;
	
	//读取文件对象
	private static Properties ps=new Properties();
	
	//静态代码块,类加载时执行,只执行一次
	static {
		
		try {
			//读文件
			//输入流:当前类的类加载器获取
			ps.load(DBUtil.class.getClassLoader().getResourceAsStream("db.properties"));
			
			//取数据
			driver=ps.getProperty("mysql_driver");
			url=ps.getProperty("mysql_url");
			user=ps.getProperty("mysql_user");
			password=ps.getProperty("mysql_pw");
			Class.forName(driver);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
	}
	//封装连接方法
	public static Connection getConnection() throws SQLException {
		
		return DriverManager.getConnection(url, user, password);
		 
	}
	
	//封装关闭方法
	public static void  closeConection(
			Connection con,Statement st,ResultSet rs) throws SQLException {
		if(rs!=null) {
			rs.close();
		}
		if(st!=null) {
			st.close();
		}
		if(con!=null) {
			con.close();
			}
		
	}
	
}

//连接池方式
import org.apache.commons.dbcp.BasicDataSource;

/**
 * 工具类
 *  1.读文件取数据
 * 2.封装连接、关闭的方法
 * 
 * */
public class DBCPUtil {
	//定义四个变量接收数据
	private static String driver=null;
	private static String url=null;
	private static String user=null;
	private static String password=null;
	
	//读取文件对象
	private static Properties ps=new Properties();
	//连接池对象
	private static BasicDataSource ds=new BasicDataSource();
	
	//静态代码块,类加载时执行,只执行一次
	static {
		
		try {
			//读文件
			//输入流:当前类的类加载器获取
			ps.load(DBCPUtil.class.getClassLoader().getResourceAsStream("db.properties"));
			
			//取数据
			driver=ps.getProperty("mysql_driver");
			url=ps.getProperty("mysql_url");
			user=ps.getProperty("mysql_user");
			password=ps.getProperty("mysql_pw");
			
			//获取连接池相关参数
			String initialSize=ps.getProperty("dataSource.initialSize");
			String maxIdle=ps.getProperty("dataSource.maxIdle");
			String minIdle=ps.getProperty("dataSource.minIdle");
			String maxActive=ps.getProperty("dataSource.maxActive");
			String maxWait=ps.getProperty("dataSource.maxWait");
			//设置mysql参数
			ds.setDriverClassName(driver);
			ds.setUrl(url);
			ds.setUsername(user);
			ds.setPassword(password);
			//设置连接池参数
			if(initialSize!=null) {
				ds.setInitialSize(Integer.parseInt(initialSize));
			}
			if(maxIdle!=null) {
				ds.setMaxIdle(Integer.parseInt(maxIdle));
			}
			if(minIdle!=null) {
				ds.setMinIdle(Integer.parseInt(minIdle));
			}
			if(maxActive!=null) {
				ds.setMaxActive(Integer.parseInt(maxActive));
			}
			if(maxWait!=null) {
				ds.setMaxWait(Long.parseLong(maxWait));
			}
		
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
	}
	//封装连接方法
	public static Connection getConnection() throws SQLException {
		Connection con=null;
		if(ds!=null) {
			con=ds.getConnection();
			
		}
		return con;
		 
	}
	
	//封装归还方法
	public static void  releaseConection(
			Connection con,Statement st,ResultSet rs) throws SQLException {
		if(rs!=null) {
			rs.close();
		}
		if(st!=null) {
			st.close();
		}
		if(con!=null) {
			con.close();
			}//归还
		
	}
	//主方法仅用于测试是否能成功连接
	public static void main(String [] args) throws SQLException {
		System.out.println(getConnection());
	}
	
}

3.Statement与预处理PreparedStatement语句 使用Statement语句通过伪造密码成功连接数据库 PreparedStatement可以避免这个问题（防止SQL注入）

/**
UserDaoImpl实现类
*/
package com.ylb.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ylb.dao.UserDao;
import com.ylb.entity.Emp;
import com.ylb.entity.User;
import com.ylb.util.DBUtil;

public class UserDaoImpl implements UserDao {
	User use=null;
	@Override
	public void loginByStatement(String user,String pwd) {
		Connection con=null;
		Statement st=null;
		ResultSet rs=null;
		
		
		try {
			con=DBUtil.getConnection();
			st=con.createStatement();
			String sql="select * from user_sss where username='"
			+user+"'and password='"+pwd+"'";
			System.out.println(sql);
			
			rs=st.executeQuery(sql);
			if(rs.next()) {
				System.out.println("成功");
				
			}
			else {
				System.out.println("失败");
			}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				//5.关闭
				DBUtil.closeConection(con, st, rs);
				
				
			}catch(SQLException e) {
				e.printStackTrace();
			}
			
		}
		
	}
	@Override
	public void loginByPreparedStatement(String user, String pwd) {
		Connection con=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		
		try {
			con=DBUtil.getConnection();
			String sql="select * from user_sss where username=? and password=?";
			ps=con.prepareStatement(sql);
			ps.setString(1, user);
			ps.setString(2, pwd);
			rs=ps.executeQuery();
			System.out.println(sql);
			
			if(rs.next()) {
				System.out.println("成功");
				
			}
			else {
				System.out.println("失败");
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			
			try {
				DBUtil.closeConection(con, ps, rs);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

}

/**
TestCaseUser测试类
*/
package com.ylb.test;

import org.junit.Test;

import com.ylb.dao.AccountDao;
import com.ylb.dao.UserDao;
import com.ylb.dao.impl.AccountDaoImpl;
import com.ylb.dao.impl.UserDaoImpl;
import com.ylb.factory.FactoryDao;


public class TestCaseUser {
	//UserDao dao=new UserDaoImpl();
	UserDao dao=(UserDao) FactoryDao.getInstance("UserDao");
	@Test
	public void testOne() {
		//正确的用户名和密码
	dao.loginByStatement("张三","666");
	//正确的用户名 伪造密码
	//实际结果,"登录成功"
	//伪造密码  'a' or '1'='1' 永真式
	dao.loginByStatement("张三","a' or '1'='1");
	}
	@Test
	public void testTwo() {
		dao.loginByPreparedStatement("李四", "888");
		//预期结果失败，实际结果失败
		//显示实际结果"登录失败"，该语句对象预防SQL注入(安全性)
		//伪造密码 'a' or '1'='1' 永真式
		dao.loginByPreparedStatement("张三","'a' or '1'='1'");
	}
}

/**
实体类中的User对象
*/
package com.ylb.entity;

public class User {
	private String user;
	public String getUser() {
		return user;
	}
	public void setUser(String user) {
		this.user = user;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public User() {
		super();
		// TODO Auto-generated constructor stub
	}
	public User(String user, String password) {
		super();
		this.user = user;
		this.password = password;
	}
	private String password;
}

4.连接数据在数据库中模拟操作A,B之间转账,修改提交方式改为手动提交。在中间位置制造异常,有异常转账失败进行回滚。

/**
AccountDaoImpl实现类的主要内容
*/
public class AccountDaoImpl implements AccountDao {

	@Override
	public void translate(String from, String to, int num) {
		// PreparedStatement
		Connection con=null;
		PreparedStatement ps=null;
		String sql=null;
		try {
		//更新A账户(减小)
			con=DBCPUtil.getConnection();
			con.setAutoCommit(false);
			sql="update account set money=money-? where id=?";
			ps=con.prepareStatement(sql);
			ps.setInt(1,num);
			ps.setString(2,from);
			ps.executeUpdate();
			
		//制造异常
		//Integer.parseInt("abc");
		
		//更新B账户(增加)
			
			sql="update account set money=money+? where id=?";
			ps.setInt(1,num);
			ps.setString(2,to);
			ps.executeUpdate();
			con.commit();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			try {
				//事务回滚
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}finally {
			try {
				//归还连接
				DBCPUtil.releaseConection(con, ps, null);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

}

/**
测试
*/
public class TestCaseAccount {
	AccountDao da=new AccountDaoImpl();
	@Test
	public void testTranslate() {
		da.translate("A", "B", 500);
		
	}
}

5.测试JDBC数据库连接使用Limit分页操作

/**分页的实现类
*/
public class LimitDaoImpl implements LimitDao {

	@Override
	public List<Emp> findByPage(int page, int pageSize) {
		Connection con=null;
		PreparedStatement ps=null;
		ResultSet rs=null;//结果集
		String sql=null;
		List<Emp> emps=new ArrayList<Emp>();
		try {
			con=DBCPUtil.getConnection();
		
			sql="select * from emp_sss limit ?,?";
			ps=con.prepareStatement(sql);
			int begin=(page-1)*pageSize;
			ps.setInt(1,begin);
			ps.setInt(2,pageSize);
			
			rs=ps.executeQuery();
			Emp emp=null;
			
			while(rs.next()) {
				 emp=new Emp();
					//取数据给对象赋值
					 //rs.getXXX(字段名,数字);
					 //字段:查询结果集返回的字段
					 //数字:查询结果集返回字段的下标
					 emp.setEmpno(rs.getInt("empno"));
					 emp.setEname(rs.getString("ename"));
					 emp.setPosition(rs.getString("position"));
					 emp.setSalary(rs.getDouble("salary"));
					 emp.setBonus(rs.getDouble("bonus"));
					 emp.setHiredate(rs.getDate("hiredate"));
					 emp.setLeader(rs.getInt("leader"));
					 emp.setDeptno(rs.getInt("deptno"));
					 //对象添加到集合
					 emps.add(emp);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				DBCPUtil.releaseConection(con, ps, null);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return emps ;
	}
	
}

/**
 * 实体类:封装员工对象
 *
 * */
public class Emp {
	private int empno;
	@Override
	public String toString() {
		return "Emp [empno=" + empno + ", ename=" + ename + ", position=" + position + ", salary=" + salary + ", bonus="
				+ bonus + ", hiredate=" + hiredate + ", leader=" + leader + ", deptno=" + deptno + "]";
	}
	public int getEmpno() {
		return empno;
	}
	public void setEmpno(int empno) {
		this.empno = empno;
	}
	public String getEname() {
		return ename;
	}
	public void setEname(String ename) {
		this.ename = ename;
	}
	public String getPosition() {
		return position;
	}
	public void setPosition(String position) {
		this.position = position;
	}
	public double getSalary() {
		return salary;
	}
	public void setSalary(double salary) {
		this.salary = salary;
	}
	public double getBonus() {
		return bonus;
	}
	public void setBonus(double bonus) {
		this.bonus = bonus;
	}
	public Date getHiredate() {
		return hiredate;
	}
	public void setHiredate(Date hiredate) {
		this.hiredate = hiredate;
	}
	public int getLeader() {
		return leader;
	}
	public void setLeader(int leader) {
		this.leader = leader;
	}
	public int getDeptno() {
		return deptno;
	}
	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}
	private String ename;
	private String position;
	private double salary;
	private double bonus;
	private Date hiredate;
	private int leader;
	private int deptno;
}

/**
测试
*/
public class TestCaseLimit {
	LimitDao dao=new LimitDaoImpl();
	@Test
	public void testLimit() {
		List<Emp> emps=dao.findByPage(3, 5);
		
		for(Emp emp:emps) {
			System.out.println(emp.toString());
		}
	}
}

6.批量插入数据与删除

/**
批量插入与删除
*/
public class StudentDaoImpl implements StudentDao {
	//批量添加
	@Override
	public void insertBatch() {
		//Statement
		Connection con=null;
		Statement st=null;
			
		try {
			con=DBCPUtil.getConnection();
			con.setAutoCommit(false);
			st=con.createStatement();
			//执行
			//通过循环方式构造若干待处理SQL语句
			String sql=null;
			for(int i=1;i<=100;i++) {
				//构造SQL
				sql="insert into stu_sss values("+i+",'张三丰')";
					
				st.addBatch(sql);
				//添加到语句对象列表
			}
			st.executeBatch();
			st.clearBatch();
			//事务提交
			con.commit();
		} catch (Exception e) {
			
			e.printStackTrace();
			
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}finally {
			try {
				//归还
				DBCPUtil.releaseConection(con, st, null);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}
	//批量删除
	@Override
	public void deleteBatch() {
		Connection con=null;
		PreparedStatement ps=null;
		
		try {
			con=DBCPUtil.getConnection();
			con.setAutoCommit(false);
			String sql="delete from stu_sss where id=?";
			ps=con.prepareStatement(sql);
			for(int i=1;i<=100;i++) {
				
		
			ps.setInt(1,i);
			ps.addBatch();
			}
			ps.executeBatch();
			ps.clearBatch();
			//事务提交
			con.commit();
			
		} catch (SQLException e) {
		
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}finally {
			try {
				DBCPUtil.releaseConection(con, ps, null);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}

	}

}

7.工厂设计模式:将需要的对象通过属性配置文件写到config.properties中 在工厂类中创建对象。

/**
 * 工厂类
 * 
 * */
public class FactoryDao {

	public static Object getInstance(String type) {
		Object obj=null;
	/*	if("UserDao".equals(type)) {
			obj=new UserDaoImpl();
		}*/
	//获取配置文件的路径值 通过反射实例
		
		/*
		 * 设计模式:为了解决一类相同或相似问题而提出
		 * 来的解决方案并为其命名
		 * 工厂设计模式:为了放回一个符合接口要求的对象
		 * 方式发生改变不会影响调用者
		 * */
		try {
			obj=Class.forName(ConfigUtil.getValue(type)).newInstance();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		return obj;
	}
	public static void main(String[] args) {
		System.out.println(getInstance("UserDao"));
	}
}

7.添加一个主键自增的数据

public class DeptAndEmpDaoImpl implements DeptAndEmpDao {

	@SuppressWarnings("resource")
	@Override
	public void deptAndEmp(Dept dept, Emp emp) {
		Connection con=null;
		PreparedStatement ps=null;
		//接收自增类型的数据
		ResultSet rs=null;
		String sql=null;
		try {
			//1.连接
			con=DBCPUtil.getConnection();
			con.setAutoCommit(false);
			//2.语句对象
			//部门表插入数据
			sql="insert into dept_sss(dname,location) values(?,?)";
			ps=con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			//常量的具体指为1
			ps.setString(1,dept.getDname());
			ps.setString(2,dept.getLocation());
			ps.executeUpdate();
			
			//返回自增类型数据(deptno)
			rs=ps.getGeneratedKeys();
			//取部门号
			int deptno=0;
			if(rs.next()) {
				deptno=rs.getInt(1);//字段拿不到
			}
			System.out.println("自增部门号:"+deptno);
			//员工表插入数据
			//语句对象
			//员工号不需要返回
			sql="insert into emp_sss(ename,deptno) values(?,?)";
			ps=con.prepareStatement(sql);
			ps.setString(1,emp.getEname());
			//返回自增类型部门号
			ps.setInt(2,deptno);
			//执行
			ps.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}finally {
			try {
				DBCPUtil.releaseConection(con, ps, rs);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}

}

8.涉及多表查询时可以在entity实体类中创建多个对象的实体类。如DeptAndEmpl类 将Emp对象和Dept对象所需要的属性添加进去。

ps:查询用:executeQuery()
修改添加删除:executeUpdate()