一、步骤
- 导入驱动包 mysql-connector-java
- 注册驱动 Driver
- 建立连接 Connection
- Statemen t执行SQL语句
- 查询操作时，ResultSet 存放结果
二、建立连接以及简单增删改查
public class JDBCTest {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
// 加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// 获得连接
String url = "jdbc:mysql://localhost:3306/test?serverTimezone=UTC";
// ?useUnicode=true&characterEncoding=UTF-8
//?useSSL=false&serverTimezone=UTC
String username = "root";
String password = "密码";
connection = DriverManager.getConnection(url, username, password);
// 增
String sql1 = "insert into people1(id,name,hobby,birthday) values(4,'小花','乒乓球','1989-7-4')";
// 删
String sql2 = "delete from people1 where id=2";
// 改
String sql3 = "update people1 set name= '小红' where id=1";
// 查
String sql4 = "select * from people1";
// 执行增删改
statement = connection.createStatement();
statement.executeUpdate(sql3);
// 执行查
resultSet = statement.executeQuery(sql4);
// 输出查询结果
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String hobby = resultSet.getString("hobby");
Date date = resultSet.getDate("birthday");
System.out.println(id + "," + name + "," + hobby + "," + date);
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
// 资源释放
finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
// 更快更早执行垃圾回收机制
connection = null;
}
if (statement != null) {
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
// 更快更早执行垃圾回收机制
statement = null;
}
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
// 更快更早执行垃圾回收机制
resultSet = null;
}
}
}
}
三、Statement与PreparedStatement
Statement：拼接sql语句且有sql注入风险（用户恶意编写sql语句）
String name1="小明";
String sql3="update people1 set name='"+name1+"'where id=1";
Statement statement = connection.createStatement();
statement.executeUpdate(sql3);
PreparedStatement：支持占位符？且无sql注入风险
String name1="小王";
String sql3="update people1 set name= ? where id=1";
PreparedStatement preparedStatement = connection.prepareStatement(sql3);
// 位置，值
preparedStatement.setString(1, name1);
preparedStatement.executeUpdate();
四、建立连接的工具类
简化了重复代码
import java.sql.*;
/**
* JDBC工具类
*/
public class JDBCTools {
private static String url = "jdbc:mysql://localhost:3306/test?serverTimezone=UTC";
private static String user = "root";
private static String password = "密码";
// 注册驱动
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 取得连接
*/
public static Connection getConection() {
Connection connection = null;
try {
connection = DriverManager.getConnection(url, user, password);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return connection;
}
/**
* 释放资源
*/
public static void release(Connection connection, Statement statement, ResultSet resultSet) {
if (connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
connection = null;
}
if (statement != null) {
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
statement = null;
}
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
resultSet = null;
}
}
}
使用db.properties配置文件，在WEB-INF文件中新建classes文件夹放置配置文件
package utlis;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
/**
* JDBC工具类
*/
public class JDBCTools {
private static String driverpath;
private static String url ;
private static String user;
private static String password;
static {
// 加载配置文件
Properties properties = new Properties();
try {
String path = JDBCTools.class.getClassLoader().getResource("db.properties").getPath();
properties.load(new FileInputStream(path));
} catch (IOException e) {
e.printStackTrace();
}
// 得到配置信息
driverpath = properties.getProperty("driverpath");
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
// 注册驱动
try {
Class.forName(driverpath);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 取得连接
*/
public static Connection getConection() {
Connection connection = null;
try {
connection = DriverManager.getConnection(url, user, password);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return connection;
}
/**
* 释放资源
*/
public static void release(Connection connection, Statement statement, ResultSet resultSet) {
if (connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
connection = null;
}
if (statement != null) {
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
statement = null;
}
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
resultSet = null;
}
}
}
五、案例
Person.java
package db;
import java.sql.Date;
public class Person {
private int id;
private String name;
private String hobby;
private Date date;
public Person(int id, String name, String hobby, Date date) {
this.id = id;
this.name = name;
this.hobby = hobby;
this.date = date;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getHobby() {
return hobby;
}
public void setHobby(String hobby) {
this.hobby = hobby;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
@Override
public String toString() {
return "db.Person{" +
"id=" + id +
", name='" + name + '\'' +
", hobby='" + hobby + '\'' +
", date=" + date +
'}';
}
}
PersonRepository.java
package db;
import utlis.JDBCTools;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* 处理数据库操作
*/
public class PersonRepository {
private Connection connection=null;
private PreparedStatement preparedStatement=null;
private ResultSet resultSet=null;
public PersonRepository() {
}
/**
* 查询所有
*/
public List<Person> findall(){
List<Person> list = new ArrayList<>();
connection = JDBCTools.getConection();
String sql="select * from people1";
try {
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
Person person =null;
while (resultSet.next()){
Integer id = resultSet.getInt(1);
String name = resultSet.getString(2);
String hobby = resultSet.getString(3);
Date date = resultSet.getDate(4);
person=new Person(id,name,hobby, date);
list.add(person);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCTools.release(connection,preparedStatement,resultSet);
}
return list;
}
/**
* 按id查找
*/
public Person findById(Integer id1){
List<Person> list = new ArrayList<>();
connection = JDBCTools.getConection();
Person person =null;
try {
String sql="select * from people1 where id=?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, id1);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
Integer id = resultSet.getInt(1);
String name = resultSet.getString(2);
String hobby = resultSet.getString(3);
Date date = resultSet.getDate(4);
person=new Person(id,name,hobby, date);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCTools.release(connection,preparedStatement,resultSet);
}
return person;
}
/**
* 添加
*/
public void add(String name,String hobby,Date date){
connection=JDBCTools.getConection();
try {
String sql="insert into people1(name,hobby,birthday) values(?,?,?)";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, name);
preparedStatement.setString(2, hobby);
preparedStatement.setDate(3, date);
preparedStatement.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCTools.release(connection, preparedStatement, null);
}
}
/**
* 删除
*/
public void deleteById(int id){
connection=JDBCTools.getConection();
try {
String sql="delete from people1 where id=?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, id);
preparedStatement.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCTools.release(connection, preparedStatement, null);
}
}
/**
* 更新
*/
public void updateById(Integer id,String name,String hobby,Date date){
connection = JDBCTools.getConection();
try {
String sql="update people1 set name= ?,hobby=?,birthday=? where id=?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, name);
preparedStatement.setString(2, hobby);
preparedStatement.setDate(3,date);
preparedStatement.setInt(4, id);
preparedStatement.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCTools.release(connection, preparedStatement, null);
}
}
}
Servlet.java
package servlet;
import db.Person;
import db.PersonRepository;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Date;
import java.util.List;
@WebServlet("/test")
public class Servlet extends HttpServlet {
private PersonRepository personRepository=new PersonRepository();
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("utf-8");
resp.setCharacterEncoding("utf-8");
String method = req.getParameter("method");
if(method==null){
method="findall";
}
switch (method) {
// 删除
case "delete": {
String id1 = req.getParameter("id");
Integer id = Integer.parseInt(id1);
personRepository.deleteById(id);
resp.sendRedirect("/test");
break;
}
// 查找所有
case "findall": {
List<Person> findall = personRepository.findall();
req.setAttribute("lists", findall);
req.getRequestDispatcher("all.jsp").forward(req, resp);
break;
}
// 按id查找，更新准备
case "seleteById": {
String id1 = req.getParameter("id");
Integer id = Integer.parseInt(id1);
Person find = personRepository.findById(id);
req.setAttribute("list",find);
req.getRequestDispatcher("update.jsp").forward(req, resp);
break;
}
default:
break;
}
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("utf-8");
resp.setCharacterEncoding("utf-8");
String method = req.getParameter("method");
switch (method){
// 添加
case "add":{
String name = req.getParameter("name");
String hobby = req.getParameter("hobby");
String date1 = req.getParameter("date");
Date date = Date.valueOf(date1);
personRepository.add(name, hobby, date);
resp.sendRedirect("/test");
break;
}
// 更新
case "update":{
String id1 = req.getParameter("id");
Integer id = Integer.parseInt(id1);
String name = req.getParameter("name");
String hobby = req.getParameter("hobby");
String date1 = req.getParameter("date");
Date date = Date.valueOf(date1);
personRepository.updateById(id, name, hobby, date);
resp.sendRedirect("/test");
break;
}
default:{
break;
}
}
}
}
all.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<html>
<head>
<title>学生管理系统</title>
<script src="js/jquery-1.7.2.min.js"></script>
</head>
<body>
<div >
<table >
<tr><a href="add.jsp">添加</a></tr>
<tr>
<th>编号</th>
<th>姓名</th>
<th>爱好</th>
<th>出生日期</th>
</tr>
<c:forEach items="${lists}" var="list">
<tr>
<td>${list.id}</td>
<td>${list.name}</td>
<td>${list.hobby}</td>
<td>${list.date}</td>
<td><a href="/test?method=seleteById&id=${list.id}">修改</a></td>
<td><a href="/test?method=delete&id=${list.id}">删除</a></td>
</tr>
</c:forEach>
</table>
</div>
</body>
</html>
add.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>添加学生</title>
</head>
<body>
<form action="/test?method=add" method="post">
<table>
<tr>
<td>姓名：</td>
<td><input name="name"></td>
</tr>
<tr>
<td>爱好：</td>
<td><input name="hobby"></td>
</tr>
<tr>
<td>出生日期：</td>
<td><input name="date"></td>
</tr>
<tr>
<td><input type="submit"></td>
</tr>
</table>
</form>
</body>
</html>
update.jsp
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>gengxin</title>
</head>
<body>
<form action="/test?method=update" method="post">
<table>
<tr>
<td>编号：</td>
<td><input name="id" value="${list.id}" readonly></td>
</tr>
<tr>
<td>姓名：</td>
<td><input name="name" value="${list.name}"></td>
</tr>
<tr>
<td>爱好：</td>
<td><input name="hobby" value="${list.hobby}"></td>
</tr>
<tr>
<td>出生日期：</td>
<td><input name="date" value="${list.date}"></td>
</tr>
<tr>
<td><input type="submit" value="修改"></td>
</tr>
</table>
</form>
</body>
</html>
六、连接池C3P0
对于上面的操作，没执行一次sql操作就会形成一个连接并最终结束，会造成资源浪费。连接池是会预先为数据库创建一个缓冲池，并放入若干连接对象，每次执行操作便取出一个并于结束后归还。若没有空闲连接时，新的请求进去等待序列，直到其他线程释放连接。
- 使用前需导入C3P0 jar包
- 将配置文件c3p0-config.xml（名字不可修改）放在resources文件夹下
在输入url时使用&需要使用转义字符&
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<named-config name="mysql">
<!-- 指定连接数据源的基本属性 -->
<property name="user">root</property>
<property name="password">密码</property>
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/test?serverTimezone=UTC</property>
<!-- 若数据库中连接数不足时, 一次向数据库服务器申请多少个连接 -->
<property name="acquireIncrement">5</property>
<!-- 初始化数据库连接池时连接的数量 -->
<property name="initialPoolSize">20</property>
<!-- 数据库连接池中的最小的数据库连接数 -->
<property name="minPoolSize">5</property>
<!-- 数据库连接池中的最大的数据库连接数 -->
<property name="maxPoolSize">40</property>
</named-config>
</c3p0-config>
改写JDBC工具类
/**
* JDBC工具类
*/
public class JDBCTools {
private static ComboPooledDataSource dataSource;
static {
// 创建c3p0对象
dataSource = new ComboPooledDataSource("mysql");
}
/**
* 取得连接
*/
public static Connection getConection() {
Connection connection = null;
try {
connection = dataSource.getConnection();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return connection;
}
/**
* 释放资源
*/
public static void release(Connection connection, Statement statement, ResultSet resultSet) {
if (connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
connection = null;
}
if (statement != null) {
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
statement = null;
}
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
resultSet = null;
}
}
}
扫一扫
3295
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
