步骤
- 导入驱动jar包mysql-connector-java-8.0.23.jar
- 注册驱动
- 获取数据库连接对象 Connection
- 定义sql
- 获取执行sql语句的对象Staement
- 执行sql语句，接受返回结果
- 处理结果
- 释放资源
package jdbc.lesson1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class JdbcDemo01 {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection root = DriverManager.getConnection("jdbc:mysql://localhost:3306/shop", "root", "123456");
String sql = "update a set name='djhahd' where id=1";
Statement statement = root.createStatement();
int i = statement.executeUpdate(sql);
System.out.println(i);
statement.close();
root.close();
}
}
各个对象解释
1.DriverManager:驱动管理对象
- 注册驱动：告诉程序改执行哪一个数据库的驱动jar，真正注册驱动的是这个对象
- 获取数据库连接：
? 参数：url：指定连接的路径
? 语法：jdbc:mysql://ip地址(域名):端口号/数据库名称
? 细节：如果连接本机可以省写为：jdbc:mysql:///数据库名称
注意：mysql5之后的驱动jar包可以省略注册驱动的步骤
2.Connection:数据库连接对象
- 获取执行sql的对象
- 管理事务：
- 开启事务 setAutoCommit
- 提交事务 commit
- 回滚事务 rollback
3.Statement:执行sql的对象
- 执行sql语句
- executeUpdate:执行DML语句或DDL语句,返回值是影响的行数，是一个int类型
- executeQuery:执行DQL语句，返回结果集
4.ResultSet:结果集对象，封装查询结果
-
next:游标向下移动一行，判断当前行是否是最后有行 -
getxxx():获取数据
- xx代表数据类型
- 获取int类型时，需要列的编号，从1开始
- 获取string时，需要列的名称
注意： 使用循环判断游标是否是最后一行
5.PreparedStatement:执行sql的对象(防注入问题)
- 先预编译：参数使用？占为符
- 定义sql语句，值用？代替
- 获取sql的执行对象
- 给？赋值
- 方法：setxxx（）
- 参数1.为？的位置，1开始
- 参数2为值
- 执行sql语句，不需要传递sql语句
释放资源时尽量判断是否为空，防止空指针异常
将数据库中数据表的数据封装为对象，然后装载集合，返回
1.定义一个类
package cn.itcast.damain;
import java.util.Date;
public class Emp {
private int id;
private String name,pwd,sex;
private Date birthday;
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 getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
@Override
public String toString() {
return "Emp{" +
"id=" + id +
", name='" + name + '\'' +
", pwd='" + pwd + '\'' +
", sex='" + sex + '\'' +
", birthday=" + birthday +
'}';
}
}
2.定义方法
package jdbc;
import cn.itcast.damain.Emp;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class jdbcdemo2 {
public static void main(String[] args) {
List<Emp> list = new jdbcdemo2().findAll();
System.out.println(list);
}
public List<Emp> findAll(){
Connection conn = null;
Statement st = null ;
ResultSet re = null;
List<Emp> list = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql:///shop","root","123456");
String sql = "select * from a";
st = conn.createStatement();
re = st.executeQuery(sql);
Emp emp =null;
list = new ArrayList<Emp>();
while (re.next()){
int id = re.getInt("id");
String name = re.getString("name");
String pwd = re.getString("pwd");
String sex = re.getString("sex");
Date birthday = re.getDate("birthday");
emp = new Emp();
emp.setId(id);
emp.setName(name);
emp.setBirthday(birthday);
emp.setPwd(pwd);
emp.setSex(sex);
list.add(emp);
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally {
if (re!=null){
try {
re.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
if (st!=null){
try {
st.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
if (conn!=null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
}
return list;
}
}
3.实现方法
List<Emp> list = new jdbcdemo2().findAll();
System.out.println(list);
JDBC工具类
获取配置文件的路径
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?characterEncoding=utf8&useSSL=true&serverTimezone=UTC&rewriteBatchedStatements=true
username=root
password=123456
package com.tzeao.test.lesson01.utils;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdvcUtils {
private static String url = null;
private static String username = null;
private static String password = null;
static {
try {
InputStream in = JdvcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);
String driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
public static void release(Connection conn, Statement st, ResultSet rs) throws SQLException {
if (rs != null) {
rs.close();
}
if (st != null) {
st.close();
}
if (conn != null) {
conn.close();
}
}
}
ClassLoader classLoader = JdvcUtils.class.getClassLoader();
URL res = classLoader.getResource(”配置文件名字“);
String path = res.getPath();
登录界面
package jdbc.lesson1;
import com.tzeao.test.lesson01.utils.JdvcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class jdbcDemo02 {
public static void main(String[] args) {
Scanner scanner = new Scanner(System.in);
String name = scanner.next();
String password = scanner.next();
Boolean jdbcDemo02 = new jdbcDemo02().login(name, password);
if (jdbcDemo02){
System.out.println("成功");
}else {
System.out.println("失败");
}
}
public Boolean login(String usernmae,String pwssword){
if (usernmae == null || pwssword == null){
return false;
}
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdvcUtils.getConnection();
String sql = "select * from users where `NAME`='"+usernmae+"'and`PASSWORD`='"+pwssword+"'";
st = conn.createStatement();
rs = st.executeQuery(sql);
return rs.next();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try {
JdvcUtils.release(conn,st ,rs);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return false;
}
}
事务
手动制造异常
int a =1/0
关闭自动提交就是开启事务
catch里面进行事务回滚
所有sql语句执行完毕时提交事务
数据库连接池
好处：节约资源，用户访问高效
实现：标准接口：DataSource
c3p0
c3p0-0.9.5.5.jar
mchange-commons-java-0.2.19.jar
配置
名字要命名为c3p0-config.xml
<c3p0-config> <default-config> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql://localhost/std</property> <property name="user">root</property> <property name="password">root</property> <property name="initialPoolSize">10</property> <property name="maxIdleTime">30</property> <property name="maxPoolSize">100</property> <property name="minPoolSize">10</property> <property name="maxStatements">200</property> </default-config> <named-config name="intergalactoApp"> <property name="acquireIncrement">50</property> <property name="initialPoolSize">100</property> <property name="minPoolSize">50</property> <property name="maxPoolSize">1000</property> <property name="maxStatements">0</property> <property name="maxStatementsPerConnection">5</property> <user-overrides user="master-of-the-universe"> <property name="acquireIncrement">1</property> <property name="initialPoolSize">1</property> <property name="minPoolSize">1</property> <property name="maxPoolSize">5</property> <property name="maxStatementsPerConnection">50</property> </user-overrides> </named-config></c3p0-config>
JAVA编写
conn.close();//归还
package C3P0;import com.mchange.v2.c3p0.ComboPooledDataSource;import javax.sql.DataSource;import java.sql.Connection;import java.sql.SQLException;public class C3P0 { public static void main(String[] args) {
Druid
-
导入jar包 druid-1.2.3.jar -
配置文件 druid.properties driverClassName=com.mysql.cj.jdbc.Driverurl=jdbc:mysql://localhost:3306/jdbcstudy?characterEncoding=utf8&useSSL=true&serverTimezone=UTC&rewriteBatchedStatements=trueusername=rootpassword=123456initialSize=5maxActive=10maxWait=3000maxIdle=8minIdle=3
?
- 使用
package druid;import com.alibaba.druid.pool.DruidDataSourceFactory;import javax.sql.DataSource;import java.io.InputStream;import java.sql.Connection;import java.util.Properties;public class druiddemo1 { public static void main(String[] args) throws Exception {
定义Druid工具类
package utilss;import com.alibaba.druid.pool.DruidDataSourceFactory;import javax.sql.DataSource;import java.io.InputStream;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.Properties;public class JDBCUtil {
使用
import utilss.JDBCUtil;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;public class Druidgjuleoi { public static void main(String[] args) { Connection conn = null; PreparedStatement st =null; try { conn = JDBCUtil.getConnection(); String sql = "insert into users values(?,?,?,?,?)"; st = conn.prepareStatement(sql); st.setInt(1, 9); st.setString(2, "9"); st.setString(3, "9"); st.setString(4, "9"); st.setDate(5, new java.sql.Date(System.currentTimeMillis())); int i = st.executeUpdate(); } catch (SQLException throwables) { throwables.printStackTrace(); }finally { JDBCUtil.close(st, conn); } }}
Spring JDBC
对JDBC的封装
JdbcTemplate使用步骤
-
准备DruidDataSource连接池 -
导入依赖的jar包 -
创建JdbcTemplate对象，传入Druid连接池 -
调用execute、update、queryXxx等方法
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dEGurjMr-1632574760653)(image-20210410002031321.png)]
package jdbctemplate;import org.springframework.jdbc.core.JdbcTemplate;import utilss.JDBCUtil;public class jdbcTemplateDemo { public static void main(String[] args) {
执行DML语句
package jdbctemplate;import org.junit.Test;import org.springframework.jdbc.core.JdbcTemplate;import utilss.JDBCUtil;public class jdbcTemplateDemo1 {
执行DQL语句
@Test
public void test4() {
String sql = "select * from users where id=8";
Map<String, Object> update = template.queryForMap(sql);
System.out.println(update);
}
@Test
public void test5() {
String sql = "select * from users";
List<Map<String, Object>> maps = template.queryForList(sql);
System.out.println(maps);
}
@Test
public void test6() {
String sql = "select * from users";
template.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
}
@Test
public void test7() {
String sql = "select count(id) from users";
Long aLong = template.queryForObject(sql, Long.class);
System.out.println(aLong);
}
拓展
package jdbctemplate;
import org.junit.Test;
public class jdbcTemplateDemo1 {
@Test
public void test1(){
System.out.println("asjkdn");
}
}
|