15、JDBC
实验环境搭建
CREATE TABLE users(
id INT PRIMARY KEY,
`name` VARCHAR(40),
`password`VARCHAR(40),
email VARCHAR(60),
birthday DATE
);
INSERT INTO users(id,`name`,`password`,email,birthday)
VALUES (1,'张三','123456','zs@qq.com','2000-01-01');
INSERT INTO users(id,`name`,`password`,email,birthday)
VALUES (2,'李四','123456','ls@qq.com','2000-01-01');
INSERT INTO users(id,`name`,`password`,email,birthday)
VALUES (3,'王五','123456','ww@qq.com','2000-01-01');
SELECT * FROM users;
导入数据库依赖:
<dependencies>
<!-- mysql的驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.23</version>
</dependency>
IDEA中连接数据库:
JDBC固定步骤:
1.加载驱动
2.连接数据库,代表数据库
3.向数据库发送SQL的对象Statement:CRUD
4.编写SQL(根据业务,不同的SQL)
5.执行SQL
6.关闭连接
package com.kuang.test;
import com.mysql.jdbc.Driver;
import java.sql.*;
public class TestJdbc {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//配置信息
//useUnicode=true&characterEncoding=utf-8解决中文乱码
String url="jdbc:mysql://localhost:3306/jdbc?serverTimezone=GMT&useUnicode=true&characterEncoding=utf-8&useSSL=true";
String username = "root";
String password = "123456";
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.连接数据库 代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
//3.向数据库发送SQL的对象Statement CRUD
//PreparedStatement预编译 安全的连接
Statement statement = connection.createStatement();
//4.编写SQL
String sql = "select * from users";
//5.执行SQL 返回一个ResultSet:结果集
ResultSet rs = statement.executeQuery(sql);
while (rs.next()){
System.out.println("id="+rs.getObject("id"));
System.out.println("name="+rs.getObject("name"));
System.out.println("password="+rs.getObject("password"));
System.out.println("email="+rs.getObject("email"));
System.out.println("birthday="+rs.getObject("birthday"));
}
//6.关闭连接,释放资源(一定要做 先开后关)
rs.close();
statement.close();
connection.close();
}
}
预编译SQL
package com.kuang.test;
import java.sql.*;
public class TestJDBC2 {
public static void main(String[] args) throws Exception, SQLException {
//配置信息
//useUnicode=true&characterEncoding=utf-8解决中文乱码
String url="jdbc:mysql://localhost:3306/jdbc?serverTimezone=GMT&useUnicode=true&characterEncoding=utf-8&useSSL=true";
String username = "root";
String password = "123456";
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.连接数据库 代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
//3.编写SQL
String sql = "insert into users(id,`name`,password,email,birthday) values(?,?,?,?,?)";
//i受影响的行数
// String sql = "delete from users where id = 4";
//4预编译
//PreparedStatement预编译 安全的连接
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,5);//给第一个占位符?的值赋值为1;
preparedStatement.setString(2,"狂神说Java");//给第二个占位符?的值赋值为"狂神说Java";
preparedStatement.setString(3,"123456");//给第三个占位符?的值赋值为"123456";
preparedStatement.setString(4,"24736743@qq.com");//给第四个占位符?的值赋值为24736743@qq.com;
preparedStatement.setDate(5,new Date(new java.util.Date().getTime()));//给第五个占位符?的值赋值为new Date(new java.util.Date().getTime();
//5 执行SQL
int i = preparedStatement.executeUpdate();
if (i>0){
System.out.println("插入成功");
}
//6.关闭连接,释放资源(一定要做 先开后关)
preparedStatement.close();
connection.close();
}
}
事物
要么都成功,要么都失败!
ACID原则:保证数据的安全。
1.开启事物
2.事物提交 commit()
3.事物回滚 rollback()
4.关闭事物
转账:
A:1000
B:1000
A(900)---100--->B(1100)
Junit单元测试
依赖:
<!--单元测试-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
</dependency>
简单使用:
@Test注解只有在方法上有效,只要加了这个注解的方法,就可以直接运行!
package com.kuang.test;
import org.junit.Test;
public class TestJDBC3 {
@Test
public void test(){
System.out.println("Hello");
}
}
在IDEA中出现的编写Mysql错误:还未找到解决办法:
编写时一定要开启事务
package com.kuang.test;
import org.junit.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class TestJDBC3 {
@Test
public void test() {
//配置信息
//useUnicode=true&characterEncoding=utf-8解决中文乱码
String url="jdbc:mysql://localhost:3306/jdbc?serverTimezone=GMT&useUnicode=true&characterEncoding=utf-8&useSSL=true";
String username = "root";
String password = "123456";
Connection connection = null;
//1.加载驱动
try {
Class.forName("com.mysql.cj.jdbc.Driver");
//2.连接数据库 代表数据库
connection = DriverManager.getConnection(url, username, password);
// //3通知数据库开启事物
connection.setAutoCommit(false);//false是开启
String sql ="update account set money = money - 100 where name = 'A'";
connection.prepareStatement(sql).executeUpdate();
//制造错误
// int i = 1/0;
String sql2 ="update account set money = money + 100 where name = 'B'";
connection.prepareStatement(sql2).executeUpdate();
connection.commit();//以上两条sql都执行成功了,就提交事务
System.out.println("success");
} catch (Exception e) {
try {
//如果出现异常就通知数据库回滚事务
connection.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}finally {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
SMBMS(超市订单管理系统)
项目如何搭建
考虑使用不使用Maven? 依赖 jar
项目搭建准备工作
1.搭建一个maven web项目
2.配置Tomcat
3.测试项目是否能够跑起来
4.导入项目中需要的jar包
jsp Servlet mysql驱动 jstl standar…
5.创建项目包结构
6.编写实体类
ORM映射: 表-类映射
7.编写基础公共类
1.数据库配置文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/?serverTimezone=GMT&useUnicode=true&characterEncoding=utf-8
username=root
password=123456
2 .编写数据库的公共类
package com.kuang.dao;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Collections;
import java.util.Properties;
//操作数据库的公共类
public class BaseDao {
private static String driver;
private static String url;
private static String username;
private static String password;
//静态代码块,类加载的时候就初始化
static {
Properties properties = new Properties();
InputStream is = BaseDao.class.getClassLoader().getResourceAsStream("db.properties");
try {
properties.load(is);
} catch (IOException e) {
e.printStackTrace();
}
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
}
//获取数据库的连接
public static Connection getConnection(){
Connection connection = null;
try {
Class.forName(driver);
connection = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
e.printStackTrace();
}
return connection;
}
//编写查询公共类
public static ResultSet execute(Connection connection,PreparedStatement preparedStatement,ResultSet resultSet,String sql,Object[] params) throws SQLException {
//预编译的SQL,在后面执行就可以了
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
//setObject,占位符从1开始,但是我们的数组是从0开始
preparedStatement.setObject(i+1,params[i]);
}
//执行sql
resultSet = preparedStatement.executeQuery();
return resultSet ;
}
//编写增删改公共方法
public static int execute(Connection connection,String sql,Object[] params, PreparedStatement preparedStatement) throws SQLException {
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
//setObject,占位符从1开始,但是我们的数组是从0开始
preparedStatement.setObject(i+1,params[i]);
}
//执行sql
int updateRows = preparedStatement.executeUpdate();
return updateRows ;
}
//释放资源
public static boolean closeResource(Connection connection,PreparedStatement preparedStatement, ResultSet resultSet){
boolean flag = true;
if (resultSet!=null){
try {
resultSet.close();
//GC回收
resultSet = null;
} catch (SQLException e) {
e.printStackTrace();
flag = false;//没有释放成功
}
}
if (preparedStatement!=null){
try {
preparedStatement.close();
//GC回收
preparedStatement = null;
} catch (SQLException e) {
e.printStackTrace();
flag = false;//没有释放成功
}
}
if (connection!=null){
try {
connection.close();
//GC回收
connection = null;
} catch (SQLException e) {
e.printStackTrace();
flag = false;//没有释放成功
}
}
return flag;
}
}
3.编写字符编码过滤器
<!--字符编码过滤器 -->
<filter>
<filter-name>CharacterEncodingFilter</filter-name>
<filter-class>com.kuang.filter.CharacterEncodingFilter</filter-class>
</filter>
<filter-mapping>
<filter-name>CharacterEncodingFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
-
导入静态资源
登录功能实现
1.编写前端页面
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head lang="en">
<meta charset="UTF-8">
<title>系统登录 - 超市订单管理系统</title>
<link type="text/css" rel="stylesheet" href="${pageContext.request.contextPath }/css/style.css" />
<script type="text/javascript">
/* if(top.location!=self.location){
top.location=self.location;
} */
</script>
</head>
<body class="login_bg">
<section class="loginBox">
<header class="loginHeader">
<h1>超市订单管理系统</h1>
</header>
<section class="loginCont">
<form class="loginForm" action="${pageContext.request.contextPath }/login.do" name="actionForm" id="actionForm" method="post" >
<div class="info">${error }</div>
<div class="inputbox">
<label for="userCode">用户名:</label>
<input type="text" class="input-text" id="userCode" name="userCode" placeholder="请输入用户名" required/>
</div>
<div class="inputbox">
<label for="userPassword">密码:</label>
<input type="password" id="userPassword" name="userPassword" placeholder="请输入密码" required/>
</div>
<div class="subBtn">
<input type="submit" value="登录"/>
<input type="reset" value="重置"/>
</div>
</form>
</section>
</section>
</body>
</html>
2.设置首页
<!--设置欢迎页面-->
<welcome-file-list>
<welcome-file>login.jsp</welcome-file>
</welcome-file-list>
3.编写dao层用户登录接口
package com.kuang.dao.user;
import com.kuang.pojo.User;
import java.sql.Connection;
import java.sql.SQLException;
//面向接口编程
public interface UserDao {
//得到要登录的用户
public User getLoginUser(Connection connection,String userCode) throws SQLException;
}
4.编写dao接口是实现类
package com.kuang.dao.user;
import com.kuang.dao.BaseDao;
import com.kuang.pojo.User;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class UserDaoImpl implements UserDao {
public User getLoginUser(Connection connection, String userCode) throws SQLException {
PreparedStatement pstm = null;
ResultSet rs = null;
User user = null;
if (connection!=null){
String sql = "select * from smbms_user where userCode=?";
Object[] params = {userCode};
rs = BaseDao.execute(connection, pstm, rs, sql, params);
if(rs.next()){
user = new User();
user.setId(rs.getInt("id"));
user.setUserCode(rs.getString("userCode"));
user.setUserName(rs.getString("userName"));
user.setUserPassword(rs.getString("userPassword"));
user.setGender(rs.getInt("gender"));
user.setBirthday(rs.getDate("birthday"));
user.setPhone(rs.getString("phone"));
user.setAddress(rs.getString("address"));
user.setUserRole(rs.getInt("userRole"));
user.setCreatedBy(rs.getInt("createdBy"));
user.setCreationDate(rs.getTimestamp("creationDate"));
user.setModifyBy(rs.getInt("modifyBy"));
user.setModifyDate(rs.getTimestamp("modifyDate"));
}
BaseDao.closeResource(null,pstm,rs);
}
return user;
}
}
5.业务层接口
package com.kuang.service.user;
import com.kuang.pojo.User;
public interface UserService {
//用户登录
public User login(String userCode, String password);
}
6.业务层实现类
package com.kuang.service.user;
import com.kuang.dao.BaseDao;
import com.kuang.dao.user.UserDao;
import com.kuang.dao.user.UserDaoImpl;
import com.kuang.pojo.User;
import org.junit.Test;
import java.sql.Connection;
import java.sql.SQLException;
public class UserServiceImpl implements UserService{
//业务层都会调用dao层,所以我们要引入Dao层;
private UserDao userDao;
public UserServiceImpl(){
userDao = new UserDaoImpl();
}
public User login(String userCode, String password) {
Connection connection = null;
User user = null;
try {
connection = BaseDao.getConnection();
//通过业务层调用对应的具体的数据库操作
user = userDao.getLoginUser(connection, userCode);
} catch (SQLException e) {
e.printStackTrace();
}finally {
BaseDao.closeResource(connection,null,null);
}
return user;
}
// @Test
// public void test(){
// UserServiceImpl userService = new UserServiceImpl();
// User admin = userService.login("admin", "1234567");
// System.out.println(admin.getUserPassword());
//
// }
}
出现的错误 没有找到解决方法在IDEA中编写出现java.lang.NoClassDefFoundError: Could not initialize class c
解决方法: @Test报错的把BaseDao类executeQuery方法中的循环改一下,改成
for (int i = 0; i < param.length; i++) {
ppst.setObject(i+1,param【i】);
}