jdbc连接数据库
使用idea工具
首先导入jar包（驱动）自己前去官网下载
5.0版本的jar包下载地址，点击直接下载。
https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.47.zip
https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.48.zip
https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.49.zip
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RgNFJtRV-1657809570503)(C:\Users\huang\AppData\Roaming\Typora\typora-user-images\image-20220711225702894.png)]
入门测试:从数据库中获取数据
package com.hq.jdbc;
import java.sql.*;
/**
* @program:jdbc
* @description:
* @author:
* @create:2022-07-12 10:49
**/
public class JdbcDemo2 {
public static void main(String[] args) throws Exception {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接
String url="jdbc:mysql://localhost:3306/student";
String username="root";
String password="root";
Connection conn = DriverManager.getConnection(url, username, password);
//3.定义sql
String sql="select * from student_ifo";
//4.创建statement对象
Statement statement = conn.createStatement();
//5.执行结果集
ResultSet rs = statement.executeQuery(sql);
while (rs.next()){
int i1 = rs.getInt(1);//1代表数据库的列名索引 从1开始且获取的数据类型要一致
String s1 = rs.getString(2);
String s2 = rs.getString(3);
String s3 = rs.getString(4);
double Db1 = rs.getDouble(5);
double Db2 = rs.getDouble(6);
String s4 = rs.getString(7);
Date date = rs.getDate(8);
System.out.println("-------");
System.out.println(i1+","+s1+","+s2+","+s3+","+Db1+","+Db2+","+s4+","+date);
}
//释放资源
rs.close();
statement.close();
conn.close();
}
}
把从数据库中获取的数据装入到自定义的实体类中
public class JdbcDemo {
public static void main(String[] args) throws Exception {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");//可以省略
//2.获取连接 URL格式：jdbc:mysql://ip地址(域名)：端口号/数据库名
String url="jdbc:mysql://localhost:3306/student";//固定写法
String username="root";//数据库的用户名
String password="root";//密码
Connection conn = DriverManager.getConnection(url, username, password);
//3.定义sql
String sql="select * from student_ifo";//查询student_ifo中所有信息
//4.获取结果集
Statement statement = conn.createStatement();
//5.执行sql
ResultSet resultSet = statement.executeQuery(sql);
System.out.println(resultSet);
//将结果集封装到一个对象中
StudentIfo stu=new StudentIfo();
while (resultSet.next()){//遍历结果集
stu.setId(resultSet.getInt("id"));
stu.setStu_class(resultSet.getString("stu_class"));
stu.setSex(resultSet.getString("sex"));
stu.setWeight(resultSet.getDouble("weight"));
stu.setHeight(resultSet.getDouble("height"));
stu.setAddr(resultSet.getString("addr"));
stu.setBorn_birth(resultSet.getDate("born_birth"));
System.out.println(stu);
}
//6.关闭资源
statement.close();
conn.close();
}
}
//entity类
package com.hq.entity;
import java.util.Date;
/**
* @program:jdbc
* @description:
* @author:
* @create:2022-07-11 23:23
**/
public class StudentIfo {
Integer id;
String stu_class;
String name;
String sex;
Double weight;
Double height;
String addr;
Date born_birth;
public StudentIfo() {
}
public StudentIfo(Integer id, String stu_class, String name, String sex, Double weight, Double height, String addr, Date born_birth) {
this.id = id;
this.stu_class = stu_class;
this.name = name;
this.sex = sex;
this.weight = weight;
this.height = height;
this.addr = addr;
this.born_birth = born_birth;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getStu_class() {
return stu_class;
}
public void setStu_class(String stu_class) {
this.stu_class = stu_class;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Double getWeight() {
return weight;
}
public void setWeight(Double weight) {
this.weight = weight;
}
public Double getHeight() {
return height;
}
public void setHeight(Double height) {
this.height = height;
}
public String getAddr() {
return addr;
}
public void setAddr(String addr) {
this.addr = addr;
}
public Date getBorn_birth() {
return born_birth;
}
public void setBorn_birth(Date born_birth) {
this.born_birth = born_birth;
}
@Override
public String toString() {
return "StudentIfo{" +
"id=" + id +
", stu_class='" + stu_class + '\'' +
", name='" + name + '\'' +
", sex='" + sex + '\'' +
", weight=" + weight +
", height=" + height +
", addr='" + addr + '\'' +
", Born_birth=" + born_birth +
'}';
}
}
实验结果
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3PObFi9A-1657809570505)(C:\Users\huang\AppData\Roaming\Typora\typora-user-images\image-20220711234649218.png)]
数据库连接池
数据库连接池就是一个容器，负责分配、管理数据库连接
它允许应用程序重复使用一个现有的数据库连接，不再是重新建立一个
释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的连接遗漏。
好处：资源重用、提升系统响应速度、避免数据库连接遗漏
德鲁伊jar包下载：
进入官网Central Repository: com/alibaba/druid
在idea中写配置文件druid.properties
driverClassName=com.mysql.jdbc.Driver //驱动加载
url=jdbc:mysql://127.0.0.1:3306/student //注册驱动
username=root //连接数据库的用户名
password=root //连接数据库的密码。
//还有其他参数
/*
filters=stat //属性类型的字符串，通过别名的方式配置扩展插件， 监控统计用的stat 日志用log4j 防御sql注入:wall
initialSize=2 //初始化时池中建立的物理连接个数。
maxActive=300 //最大的可活跃的连接池数量
maxWait=60000 //获取连接时最大等待时间，单位毫秒，超过连接就会失效。配置了maxWait之后，缺省启用公平锁，并发效率会有所下降， 如果需要可以通过配置useUnfairLock属性为true使用非公平锁。
timeBetweenEvictionRunsMillis=60000 // 连接回收器的运行周期时间，时间到了清理池中空闲的连接，testWhileIdle根据这个判断
minEvictableIdleTimeMillis=300000
validationQuery=SELECT 1 //用来检测连接是否有效的sql，要求是一个查询语句。
testWhileIdle=true //建议配置为true，不影响性能，并且保证安全性。 申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis， 执行validationQuery检测连接是否有效。
testOnBorrow=false //申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。设置为false
testOnReturn=false //归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能,设置为flase
poolPreparedStatements=false //是否缓存preparedStatement，也就是PSCache。
maxPoolPreparedStatementPerConnectionSize=200 // 池中能够缓冲的preparedStatements语句数量
*/
与数据库进行简单的交互（增删改查）
JdbcUtils1工具类
public class JdbcUtils1 {
static String url="jdbc:mysql://localhost:3306/books";
static String username="root";
static String passwd="root";
public static Connection connSql() throws Exception {
Class.forName("com.mysql.jdbc.Driver");
return DriverManager.getConnection(url,username,passwd);
}
public static void closeConn(Connection conn,PreparedStatement ps) throws SQLException {
if (conn!=null) conn.close();
if (ps!=null) ps.close();
}
public static void closeConn1(ResultSet rs) throws SQLException {
if(rs!=null) rs.close();
}
}
public class JdbcWork {
Connection conn;
PreparedStatement ps;
ResultSet resultSet;
int rs;
long lg;
Date date;
@Before
public void conn1() throws Exception {
conn=JdbcUtils1.connSql();
// SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
// date = (Date) sdf.parse("1995-5-6 10:30:00");
}
@Test//查询所有数据
public void queryData() throws SQLException {
//定义sql
String sql="select * from book";
ps = conn.prepareStatement(sql);
resultSet = ps.executeQuery();
while (resultSet.next()){
int anInt = resultSet.getInt("id");
String string = resultSet.getString(2);
String string1 = resultSet.getString(3);
String String2 = resultSet.getString(4);
Date date = resultSet.getDate(5);
System.out.println(anInt+","+string+","+string1+","+String2+","+date);
}
}
@Test//插入数据
public void insertBook() throws SQLException {
//定义sql
String sql="insert into book values(?,?,?,?,?)";
ps=conn.prepareStatement(sql);
ps.setInt(1,0);
ps.setString(2,"肉丝写真");
ps.setString(3,"村长");
ps.setString(4,"29.00");
ps.setDate(5,new java.sql.Date(new java.util.Date().getTime()));
rs=ps.executeUpdate();
if(rs>0){
System.out.println("插入成功");
}
}
//根据id修改数据
@Test
public void updateBook() throws SQLException {
String sql="update book set author=? where id=?";
ps=conn.prepareStatement(sql);
ps.setString(1,"黄明亮");
ps.setInt(2,21);
rs= ps.executeUpdate();
if(rs>0){
System.out.println("修改成功");
}
}
@Test
public void deleteBook() throws SQLException {
String sql="delete from book where id=?";
ps=conn.prepareStatement(sql);
ps.setInt(1,20);
rs=ps.executeUpdate();
if(rs>0){
System.out.println("删除成功");
}
}
@After
public void closeAll() throws SQLException {
JdbcUtils1.closeConn1(resultSet);
JdbcUtils1.closeConn(conn,ps);
}
}
sql=“delete from book where id=?”;
ps=conn.prepareStatement(sql);
ps.setInt(1,20);
rs=ps.executeUpdate();
if(rs>0){
System.out.println(“删除成功”);
}
}
@After
public void closeAll() throws SQLException {
JdbcUtils1.closeConn1(resultSet);
JdbcUtils1.closeConn(conn,ps);
}
}
7516
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
