JDBC连接

在下BUG，有李了

已于 2022-03-31 23:13:22 修改

阅读量1k

点赞数

分类专栏： java 文章标签：学习

于 2022-03-23 22:10:26 首次发布

本文链接：https://blog.csdn.net/qq_48325875/article/details/123697601

版权

java 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

文章目录

JDBC流程

在这里插入图片描述

JDBC步骤

编写jdbc.properties文件

/** Oracle连接 */
driver = oracle.jdbc.driver.OracleDriver 
url = jdbc:oracle:thin:@127.0.0.1:1521:orcl
user = epoint
password = Gepoint
/** mysql连接 */
driver = com.mysql.jdbc.Driver 
url = jdbc:mysql://127.0.0.1:3306/epoint?useUnicode=true&characterEncoding=UTF-8
user = epoint
password = Gepoint

/** druid数据源编写格式 */
driverClassName=com.mysql.jdbc.Driver
#URL连接数据库的URL，其中travel（以下面例子来说）为连接的数据库，后面的参数可不改但不删
url=jdbc:mysql://127.0.0.1:3306/epoint?useUnicode=true&charaterEncoding=UTF-8
#安装mysql时候设置的用户与密码
username=root
password=Gepoint
#初始化物理连接的个数
initialSize=5
#最大连接池数量
maxActive=10

编写读取文件工具类

/** 读取文件工具类 */
public class ConfigUtil {
	public static String getValue(String configName) {
		if(configName == null || "".equals(configName)) {
			return "";
		}
		String configValue = "";
		ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
		configValue = bundle.getString(configName);
		if(configValue != null) {
			configValue = configValue.trim();
		}
		return configValue;
	}
}

编写连接数据库工具类

/** 数据库连接工具类 */
public class JDBCUtil {
	private static String driver = ConfigUtil.getValue("driver");
	private static String url = ConfigUtil.getValue("url");
	private static String user = ConfigUtil.getValue("user");
	private static String password = ConfigUtil.getValue("password");
	/** 连接数据库 */
	public static Connection getConnection() {
		Connection conn = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	/** 关闭连接 */
	public static void closeConnection(ResultSet rs,Statement stmt,PreparedStatement pstmt, Connection conn) {
		try {
			if(rs != null) 
				rs.close();
			if(stmt != null)
				stmt.close();
			if(pstmt != null)
				pstmt.close();
			if(conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

使用Druid数据源连接数据库

private static DataSource datasource;

/* 使用Druid读取properties文件  */
public DataSource getDataSource() {
	if (null == datasource) {		
		try {
			Properties properties = new Properties();
			properties.load(JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties"));
			datasource = DruidDataSourceFactory.createDataSource(properties);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	return datasource;
}

/* 创建连接  */
public static Connection getConnection() {
	Connection connection = null;
	try {
		datasource = new JDBCUtils().getDataSource();
		connection = datasource.getConnection();
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return connection;
}

/* 关闭连接资源 */
public static void closeConn(ResultSet rs, Statement stmt, PreparedStatement pstmt, Connection conn) {
	try {
		if (null != rs) {
			rs.close();
		}
		if (null != null) {
			stmt.close();
		}
		if (null != pstmt) {
			pstmt.close();
		}
		if (null != conn) {
			conn.close();
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}
}

测试案例

public class Employee {
	private String employeeId;
	private String name;
	private String password;
	private int age;
	private Double salary;
}

public class EmployeeDao {
	/** 新增员工 */
	public void insertEmp(Employee emp) {
		Connection conn = JDBCUtil.getConnection();
		PreparedStatement pstmt = null;
		String sql = "insert into employee values(?,?,?,?,?)";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, emp.getEmployeeId());
			pstmt.setString(2, emp.getPassword());
			pstmt.setString(3, emp.getName());
			pstmt.setInt(4, emp.getAge());
			pstmt.setDouble(5, emp.getSalary());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.closeConnection(null, null, pstmt, conn);
		}
	}
	/** 修改员工信息 */
	public void updateEmp(Employee emp) {
		Connection conn = JDBCUtil.getConnection();
		PreparedStatement pstmt = null;
		String sql = "update employee set password = ?, name = ?, age = ?, salary = ? where employeeId = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, emp.getPassword());
			pstmt.setString(2, emp.getName());
			pstmt.setInt(3, emp.getAge());
			pstmt.setDouble(4, emp.getSalary());
			pstmt.setString(5, emp.getEmployeeId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.closeConnection(null, null, pstmt, conn);
		}
	}
	/** 查询员工信息 */
	public void selectEmp(Employee emp) {
		Connection conn = JDBCUtil.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from employee where employeeId = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, emp.getEmployeeId());
			rs = pstmt.executeQuery();
			while(rs.next()) {
				Employee emp1 = new Employee();
				emp1.setEmployeeId(rs.getString("EmployeeId"));
				emp1.setPassword(rs.getString("password"));
				emp1.setName(rs.getString("name"));
				emp1.setAge(rs.getInt("age"));
				emp1.setSalary(rs.getDouble("salary"));
				System.out.println(emp1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.closeConnection(rs, null, pstmt, conn);
		}
	}
	/** 关键字查询信息 */
	public void selectEmpById(String keyword,int index,int pageSize) {
		Connection conn = JDBCUtil.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from employee where name like ? limit ?,?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, keyword);
			pstmt.setInt(2, index);
			pstmt.setInt(3,pageSize);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				Employee emp = new Employee();
				emp.setEmployeeId(rs.getString("EmployeeId"));
				emp.setPassword(rs.getString("password"));
				emp.setName(rs.getString("name"));
				emp.setAge(rs.getInt("age"));
				emp.setSalary(rs.getDouble("salary"));
				System.out.println(emp);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.closeConnection(rs, null, pstmt, conn);
		}
	}
	/** 删除员工信息 */
	public void deleteEmp(Employee emp) {
		Connection conn = JDBCUtil.getConnection();
		PreparedStatement pstmt = null;
		String sql = "delete from employee where EmployeeId = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, emp.getEmployeeId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.closeConnection(null, null, pstmt, conn);
		}
	}
	/** 查询所有员工 */
	public void selectAllEmp() {
		Connection conn = JDBCUtil.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from Employee";
		try {
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				Employee emp = new Employee();
				emp.setEmployeeId(rs.getString("EmployeeId"));
				emp.setPassword(rs.getString("password"));
				emp.setName(rs.getString("name"));
				emp.setAge(rs.getInt("age"));
				emp.setSalary(rs.getDouble("salary"));
				System.out.println(emp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.closeConnection(rs, null, pstmt, conn);
		}
	}

使用juint测试

public class JDBCTest {
	/** 测试连接数据库 */
	@Test
	public void test() {
		Connection conn = JDBCUtil.getConnection();
		System.out.println(conn);
	}
	/** 添加员工 */
	@Test
	public void testInsert() {
		EmployeeDao dao = new EmployeeDao();
		Employee emp = new Employee("1005", "是四", "1111", 22, 3000.0);
		dao.insertEmp(emp);
 	}
	/** 修改员工信息 */
	@Test
	public void testUpdate() {
		EmployeeDao dao = new EmployeeDao();
		Employee emp = new Employee("1002","sun","2222",55,8000.0);
		dao.updateEmp(emp);
	}
	/** 查询员工 */
	@Test
	public void testSelect() {
		Employee emp = new Employee();
		EmployeeDao dao = new EmployeeDao();
		emp.setEmployeeId("1002");
		dao.selectEmp(emp);
	}
	/** 关键字查询 */
	@Test
	public void testSelectKeyWord() {
		EmployeeDao dao = new EmployeeDao();
		dao.selectEmpById("%四%", 0, 3);
	}
	/** 删除员工 */
	@Test
	public void testDelete() {
		EmployeeDao dao = new EmployeeDao();
		Employee emp = new Employee();
		emp.setEmployeeId("1002");
		dao.deleteEmp(emp);
	}
	/** 查询所有员工 */
	@Test
	public void testSelectAll() {
		EmployeeDao dao = new EmployeeDao();
		dao.selectAllEmp();
	}
}

在下BUG，有李了

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
JDBC连接

文章目录JDBC步骤编写jdbc.properties文件编写读取文件工具类编写连接数据库工具类测试案例使用juint测试JDBC步骤编写jdbc.properties文件/** Oracle连接 */driver = oracle.jdbc.driver.OracleDriver url = jdbc:oracle:thin:@127.0.0.1:1521:orcluser = epointpassword = Gepoint/** mysql连接 */driver = com.mysq
复制链接

扫一扫