下载
下载地址:
https://dev.mysql.com/downloads/connector/j/
//for macOS
https://download.csdn.net/download/weixin_44084661/12646068
1. JDBC注册驱动(idea中操作)
- 项目根路径创建libs文件夹
- 复制mysql-connector-java-8.0.21.jar到libs文件夹中
- 右键libs -> add as Library
1.1 准备数据库表
CREATE DATABASE IF NOT EXISTS account DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
CREATE TABLE account (
`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
`username` VARCHAR ( 128 ) NOT NULL,
`password` VARCHAR ( 128 ) NOT NULL,
`bal` BIGINT );
INSERT INTO `account`.`account`(`id`, `username`, `password`, `bal`) VALUES (1, 'lisi', '123', 200);
INSERT INTO `account`.`account`(`id`, `username`, `password`, `bal`) VALUES (2, 'laowang', '321', 1000);
mysql> select * from account;
+----+----------+----------+------+
| id | username | password | bal |
+----+----------+----------+------+
| 1 | lisi | 123 | 200 |
| 2 | laowang | 321 | 1000 |
+----+----------+----------+------+
1.2. 创建测试类
package cn.jdbc;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.Statement;
/*
jdbc 快速入门
创建JdbcDemo1类
*/
public class JdbcDemo1 {
public static void main(String[] args) throws Exception {
//1.导入mysql驱动包 mysql-connector-java-8.0.21.jar
//2.注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");//mysql5之后,可以省略不写
// Class.forName("com.mysql.jdbc.Driver");
//报一个警告com.mysql.jdbc.Driver已经被新的方法替代com.mysql.cj.jdbc.Driver
//Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
//3.获取数据库连接对象
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/account", "root", "root@123");
//4.定义SQL语句
String sql = "UPDATE `account`.`account` SET `bal`=100 WHERE `id`=1";
//5.后去sql执行对象
Statement statement = conn.createStatement();
//6.执行SQL
int count = statement.executeUpdate(sql);
//7.查看执行结果,
System.out.println(count); //输出结果为1,
//8.释放资源
statement.close();
conn.close();
}
}
1.3 查看执行结果
mysql> select * from account;
+----+----------+----------+------+
| id | username | password | bal |
+----+----------+----------+------+
| 1 | lisi | 123 | 100 |
| 2 | laowang | 321 | 1000 |
+----+----------+----------+------+
2. JDBC ReslutSet 练习
2.1 准备数据库
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
`id` int NOT NULL AUTO_INCREMENT,
`username` varchar(128) NOT NULL,
`password` varchar(128) NOT NULL,
`bal` double unsigned zerofill DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- ----------------------------
-- Records of account
-- ----------------------------
BEGIN;
INSERT INTO `account` VALUES (1, 'lisi', '123', 0000000000000000000100);
INSERT INTO `account` VALUES (2, 'laowang', '321', 0000000000000000001000);
INSERT INTO `account` VALUES (3, 'wangfei', 'pass', 0000000000000000001100);
COMMIT;
2.2 ResultSet练习-v1
2.2.1. 创建测试类
package cn.jdbc;
import cn.domain.Account;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class JDBCDemo4 {
/*
查询数据库记录
*/
public List<Account> findAll() {
Statement stmt = null;
Connection conn = null;
ResultSet rs = null;
List<Account> list = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/account","root","root@123");
stmt = conn.createStatement();
String sql = "select * from account";
rs = stmt.executeQuery(sql);
Account account = null;
list = new ArrayList<Account>();
while (rs.next()) {
int id = rs.getInt("id");
String username = rs.getString("username");
String password = rs.getString("password");
double bal = rs.getDouble("bal");
account = new Account();
account.setId(id);
account.setUsername(username);
account.setPassword(password);
account.setBal(bal);
list.add(account);
System.out.println(id + "---" + username + "---" + password + "---" + bal);
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} finally {
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return list;
}
public static void main(String[] args) {
List<Account> list = new JDBCDemo4().findAll();
System.out.println(list);
}
}
2.2.2. 输出结果
1—lisi—123—100.0
2—laowang—321—1000.0
3—wangfei—pass—1100.0
[Account{id=1, username=‘lisi’, password=‘123’, bal=100.0}, Account{id=2, username=‘laowang’, password=‘321’, bal=1000.0}, Account{id=3, username=‘wangfei’, password=‘pass’, bal=1100.0}]
2.3 ResultSet练习-v2 utils 工具以及properties文件加载
2.3.1 配置properties文件
创建文件: src/jdbc.properties
文件内容:
url=jdbc:mysql://localhost:3306/account
username=root
password=root@123
driver=com.mysql.cj.jdbc.Driver
2.3.2 创建JDBCUtils类
创建文件: src/util/JDBCUtils.class
package cn.utils;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;
public class JDBCUtils {
private static String url;
private static String username;
private static String password;
private static String driver;
static {
//读取properties文件
try {
Properties pro = new Properties();
ClassLoader classLoader = JDBCUtils.class.getClassLoader();
URL res = classLoader.getResource("jdbc.properties");
String path = res.getPath();
pro.load(new FileReader(path));
// System.out.println(path);
url = pro.getProperty("url");
username = pro.getProperty("username");
password = pro.getProperty("password");
driver = pro.getProperty("driver");
Class.forName(driver);
} catch (ClassNotFoundException | FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
//释放资源
public static void close(ResultSet rs, Statement stmt, Connection conn) {
}
}
2.3.3.创建测试类(复制JDBCDemo4)
package cn.jdbc;
import cn.domain.Account;
import cn.utils.JDBCUtils;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class JDBCDemo5 {
/*
查询数据库记录
*/
public List<Account> findAll() {
Statement stmt = null;
Connection conn = null;
ResultSet rs = null;
List<Account> list = null;
try {
conn = JDBCUtils.getConnection();
stmt = conn.createStatement();
String sql = "select * from account";
rs = stmt.executeQuery(sql);
Account account = null;
list = new ArrayList<Account>();
while (rs.next()) {
int id = rs.getInt("id");
String username = rs.getString("username");
String password = rs.getString("password");
double bal = rs.getDouble("bal");
account = new Account();
account.setId(id);
account.setUsername(username);
account.setPassword(password);
account.setBal(bal);
list.add(account);
System.out.println(id + "---" + username + "---" + password + "---" + bal);
}
} catch ( SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.close(rs,stmt,conn);
}
return list;
}
public static void main(String[] args) {
List<Account> list = new JDBCDemo4().findAll();
System.out.println(list);
}
}
2.3.4 输出结果
1---lisi---123---100.0
2---laowang---321---1000.0
3---wangfei---pass---1100.0
[Account{id=1, username='lisi', password='123', bal=100.0}, Account{id=2, username='laowang', password='321', bal=1000.0}, Account{id=3, username='wangfei', password='pass', bal=1100.0}]
2.4 JDBC 登陆练习 代码
2.4.1. 需求: 通过键盘输入用户名密码,判断用户是否成功登陆
2.4.2. 创建测试类代码
package cn.jdbc;
import cn.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class JDBCDemo6 {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名");
String username = sc.next();
System.out.println("请输入密码");
String password = sc.next();
boolean flag = new JDBCDemo6().login(username, password);
if (flag) {
System.out.println("登陆成功");
} else {
System.out.println("用户名密码错误!");
}
}
public boolean login(String username, String password) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
if (username == null || password == null) {
return false;
}
try {
conn = JDBCUtils.getConnection();
String sql = "select * from account where username='" + username + "'and password= '" + password+"' ";
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
return rs.next();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtils.close(rs, stmt, conn);
}
return false;
}
}
2.4.3 输出结果
请输入用户名
lisi
请输入密码
123
登陆成功
3. JDBC Druid练习
3.1 Druid介绍 下载
Druid:
- 介绍:阿里巴巴计算平台事业部出品,为监控而生的数据库连接池
- 下载:https://repo1.maven.org/maven2/com/alibaba/druid/
- 项目地址:https://github.com/alibaba/druid
3.2 准备数据库
- 使用2.1章节数据库
3.3 JDBC Druid练习 代码
3.3.1 需求:通过Druid连接数据库并执行一条SQL语句
3.3.2 实现:
3.3.2.1 加载依赖包 druid-1.1.23.jar
3.3.2.2 创建工具类:
package cn.utils;JDBCDruidUtils
import cn.jdbc.DruidDemo1;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBCDruidUtils {
private static DataSource ds;
//1. 初始化
static {
Properties pro = new Properties();
try {
pro.load(JDBCDruidUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
ds = DruidDataSourceFactory.createDataSource(pro);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
//2. 获取连接
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
//3. 释放连接
public static void close(Statement stmt, Connection conn) {
close(null, stmt, conn);
}
public static void close(ResultSet rs, Statement stmt, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (conn != null) {
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public static DataSource getDatasource(){
return ds;
}
}
3.3.2.3 创建实现类:DruidDemo1
package cn.jdbc;
import cn.utils.JDBCDruidUtils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DruidDemo1 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = JDBCDruidUtils.getConnection();
String sql = "insert into account values(null,?, ?,?)";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "王五");
pstmt.setString(2, "wangwu");
pstmt.setDouble(3, 3000);
int count = pstmt.executeUpdate();
System.out.println(count);
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCDruidUtils.close(pstmt, conn);
}
}
}
3.3.3 输出结果:
结果打印为1 ,一条语句执行成功
七月 23, 2020 11:20:49 下午 com.alibaba.druid.pool.DruidDataSource info
信息: {dataSource-1} inited
1
查询数据库, 王五已经执行成功
+----+----------+----------+------------------------+
| id | username | password | bal |
+----+----------+----------+------------------------+
| 1 | lisi | 123 | 0000000000000000000100 |
| 2 | laowang | 321 | 0000000000000000001000 |
| 3 | wangfei | pass | 0000000000000000001100 |
| 4 | 王五 | wangwu | 0000000000000000003000 |
+----+----------+----------+------------------------+
4. Spring JDBC练习
4.1 Spring JDBC下载
spring JdbcTemplate相关依赖包
spring-jdbc-5.2.8.RELEASE.jar
spring-beans-5.2.8.RELEASE.jar
spring-tx-5.2.8.RELEASE.jar
spring-core-5.2.8.RELEASE.jar
spring-beans-5.2.8.RELEASE.jar
commons-logging-1.2.jar
下载 :
打包文件: https://download.csdn.net/download/weixin_44084661/12657503
仓库: https://mvnrepository.com/artifact/org.springframework
4.2 准备数据库
- 使用2.1章节数据库
4.3 Spring JDBC练习代码
4.3.1 加载4.1下载的依赖包 <略>
4.3.2 工具类创建 使用 < 3.3.2.2章节 创建的工具类>
4.3.3 创建实现类
4.3.3.1创建Account类,实例化数据库
package cn.domain;
//生成getter, setter toString方法
public class Account {
private int id;
private String username;
private String password;
private Double bal;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Double getBal() {
return bal;
}
public void setBal(Double bal) {
this.bal = bal;
}
@Override
public String toString() {
return "Account{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", bal=" + bal +
'}';
}
}
4.3.3.2 创建测试类
package cn.jdbc;
import cn.domain.Account;
import cn.utils.JDBCDruidUtils;
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import java.util.List;
import java.util.Map;
/*
jdbc template 入门
引入Junit来分别测试不同的方法
*/
public class JDBCTemplateDemo1 {
private static JdbcTemplate template = new JdbcTemplate(JDBCDruidUtils.getDataSource());
public static void main(String[] args) {
// 1.导入jar包
// 2. 创建JDBCTemplate对象
String sql = "update account set bal = ? where username =?";
int count = template.update(sql, 1222, "lisi");
System.out.println(count);
}
@Test
public void testInsert() {
String sql = "insert into account values(?,?,?,?)";
int count = template.update(sql, 5, "yangguo", "yang123", 500);
System.out.println(count);
}
@Test
public void testDelete() {
String sql = "delete from account where username=?";
int count = template.update(sql, "yangguo");
System.out.println(count);
}
//返回单条记录
@Test
public void testQueryForMap(){
String sql = "select * from account where username=?";
Map<String, Object> map = template.queryForMap(sql,"lisi");
System.out.println(map);
//打印结果: {id=1, username=lisi, password=123, bal=1222.0}
}
//返回结果集合
@Test
public void testQueryForList(){
String sql = "select * from account";
List<Map<String, Object>> list = template.queryForList(sql);
for (Map<String, Object> map : list) {
System.out.println(map);
}
/*打印结果
{id=1, username=lisi, password=123, bal=1222.0}
{id=2, username=laowang, password=321, bal=1000.0}
{id=3, username=wangfei, password=pass, bal=1100.0}
{id=4, username=王五, password=wangwu, bal=3000.0}
*/
}
//封装account对象为list集合
@Test
public void testQuery(){
String sql = "select * from account";
List<Account> query = template.query(sql, new BeanPropertyRowMapper<>(Account.class));
for (Account account : query) {
System.out.println(account);
}
/*
Account{id=1, username='lisi', password='123', bal=1222.0}
Account{id=2, username='laowang', password='321', bal=1000.0}
Account{id=3, username='wangfei', password='pass', bal=1100.0}
Account{id=4, username='王五', password='wangwu', bal=3000.0}
*/
}
//查询记录总数
@Test
public void testQueryForObject(){
String sql = "select count(*) from account";
Long along = template.queryForObject(sql, long.class);
System.out.println(along);
System.out.println();
}
//返回 5
}