Maven配置和使用
JDBC
jdbc是什么
-jdbc是接口,是一套java应用程序连接数据库的标准 -数据库厂商,根据jdbc接口,写了实现类—驱动 -学会JDBC,可以掌握对所有数据库的操作
jdbc的使用
注册驱动
//1.注册驱动
//1.1导包
//1.2注册驱动
Class.forName(“com.mysql.jdbc.Driver”);
创建连接对象java.sql.Connection conn
//2.创建连接对象
//jdbc:mysql://数据库IP地址:端口号/库名
String url=“jdbc:mysql://localhost:3306/mysql”;
String username=“root”;
String password=“root”;
Connection conn=DriverManager
.getConnection(url, username, password);
获得语句对象java.sql.Statement sta
//3.1拼写sql语句
String ddl=“create table dept (”
+ “id int,”
+ “name varchar(20),”
+ “password varchar(20));”;
//3.2获得语句对象
Statement sta=conn.createStatement();
//3.3执行sql语句
// sta.execute(sql)—全能,推荐ddl,dcl
//-返回值
//返回了结果集–true
//返回了int值–false
//抛异常,说明执行失败
// sta.executeUpdate(sql)–dml
// sta.executeQuery(sql)–dql
// boolean flag=sta.execute(ddl);
System.out.println(flag);
// String dml=“insert into dept values”
// + “(1,‘lilei’,‘123’)”;
// int n=sta.executeUpdate(dml);
// System.out.println(n);
String dql="select name as a ,password as p"
+ " from dept where id=1";
ResultSet rs=sta.executeQuery(dql);
// System.out.println(rs);
//ResultSet一出现,就上while
处理结果集
while(rs.next()){
// String str=rs.getString(“name”);
//sql语句中,有了别名,那么原列名就找不到了
// String str=rs.getString(“a”);
//如果使用下标,注意从1开始
String str=rs.getString(2);
System.out.println(str);
}
关闭连接
conn.close();
封装
初步封装
public class DBUtils {
static String drvierclassname=
“com.mysql.jdbc.Driver”;
static String url=“jdbc:mysql://”
+ “localhost:3306/mysql”;
static String username=“root”;
static String password=“root”;
public static Connection getConnection(){
Connection conn=null;
try {
Class.forName(drvierclassname);
conn=DriverManager
.getConnection(url, username, password);
return conn;
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
public static void closeConnection(Connection conn){
if (conn!=null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
public static void main(String[] args) {
Connection conn=DBUtils.getConnection();
try {
Statement sta=conn.createStatement();
String sql=“select * from dept”;
ResultSet rs=sta.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString(2));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
DBUtils.closeConnection(conn);
}
封装进阶
Properties在java.util包下
Properties专门为了处理在resource中的后缀为properties文件
*.properties里面都是键值对
Properties实现了map接口
*.properties键值对都必须是String
*.properties中"="左右都没有空格
public static void main(String[] args) throws IOException {
//1.获得properties对象
Properties cfg=new Properties();
//2.获取db.properties文件的流
InputStream in=Demo5.class
.getClassLoader()
.getResourceAsStream("db.properties");
// System.out.println(in);
//3.调用load方法
cfg.load(in);
//4.通过cfg.getProperty(key)获得值
String driver=cfg.getProperty(“jdbc.driver”);
String url=cfg.getProperty(“jdbc.url”);
String username=cfg.getProperty(“jdbc.username”);
String password=cfg.getProperty(“jdbc.password”);
System.out.println(driver);
System.out.println(url);
System.out.println(password);
System.out.println(username);
}
单线程完整封
public class DButil {
private static String drvier;
private static String url;
private static String username;
private static String password;
static{
Properties cfg=new Properties();
InputStream in=DButil
.class
.getClassLoader()
.getResourceAsStream("db.properties");
try {
cfg.load(in);
drvier=cfg.getProperty("jdbc.driver");
url=cfg.getProperty("jdbc.url");
username=cfg.getProperty("jdbc.username");
password=cfg.getProperty("jdbc.password");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection(){
try {
Class.forName(drvier);
Connection conn=DriverManager
.getConnection(url, username, password);
return conn;
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
public static void closeConnection(Connection conn){
if (conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
连接池
DBCP
BasicDataSource
4个必选参数
setDriverClassName
setUrl
setUserName
setPassword
多个管理策略参数
setInitialSize
setMaxActive
知识点测试代码
public class Demo2 {
//在这个测试类中,测试使用连接池获得conn对象
//在DBUtils中,把所有单线程连接,改成连接池
//测试连接池的连接上限
public static void main(String[] args) throws Exception {
String driver="com.mysql.jdbc.Driver";
String url="jdbc:mysql://localhost:3306/mysql";
String username="root";
String password="root";
//创建一个连接池对象
BasicDataSource bds=new BasicDataSource();
//连接池的必选参数
bds.setDriverClassName(driver);
bds.setUrl(url);
bds.setUsername(username);
bds.setPassword(password);
//连接池的管理策略参数
//初始化连接数
bds.setInitialSize(2);
//最大连接数
bds.setMaxActive(2);
Connection conn=bds.getConnection();
String sql="select 'hello' as a from dual";
Statement sta=conn.createStatement();
ResultSet rs=sta.executeQuery(sql);
while(rs.next()){
System.out.println(rs.getString(1));
}
conn.close();
}
}
整合DBUtils
public class DBUtils {
private static String driver;
private static String url;
private static String username;
private static String password;
private static int init;
private static int maxactive;
private static BasicDataSource bds=null;
static{
bds=new BasicDataSource();
Properties cfg=new Properties();
InputStream inStream=DBUtils.class
.getClassLoader()
.getResourceAsStream("db.properties");
try {
cfg.load(inStream);
driver=cfg.getProperty("jdbc.driver");
url=cfg.getProperty("jdbc.url");
username=cfg.getProperty("jdbc.username");
password=cfg.getProperty("jdbc.password");
init=Integer.parseInt(cfg.getProperty("init"));
maxactive=Integer.parseInt(cfg.getProperty("maxactive"));
//设置BasicDataSource必要参数
bds.setDriverClassName(driver);
bds.setUrl(url);
bds.setUsername(username);
bds.setPassword(password);
//设置BasicDataSource管理策略参数
bds.setInitialSize(init);
bds.setMaxActive(maxactive);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection(){
Connection conn=null;
try {
conn=bds.getConnection();
return conn;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new RuntimeException(e);
}
}
public static void closeConnection(Connection conn){
if (conn!=null) {
try {
//这里的close不是关闭的意思
//归还
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
连接池等待归还的演示
public class Demo3 {
public static void main(String[] args) {
Thread t1=new DemoThread(5000, “连接1”);
Thread t2=new DemoThread(6000, “连接2”);
Thread t3=new DemoThread(7000, “连接3”);
t1.start();
t2.start();
t3.start();
}
}
class DemoThread extends Thread{
int wait;//睡的毫秒数
String connName;//连接的名字
//构造方法
public DemoThread(int wait,String connName){
this.wait=wait;
this.connName=connName;
}
@Override
public void run() {
Connection conn=DBUtils.getConnection();
System.out.println(connName+" 连接成功");
try {
String sql="select 'hello' as"
+ " a from dual";
Statement sta=conn.createStatement();
ResultSet rs=sta.executeQuery(sql);
while(rs.next()){
System.out.println(connName+" "
+ " "+rs.getString(1));
}
Thread.sleep(wait);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DBUtils.closeConnection(conn);
}
}
}
PreparedStatement
重用执行计划
sql语句使用?来占位置, 使用ps.setXXX(index,值)
其他的使用方式与statem类似
优化sql执行效率
他解决了sql注入问题
使用PreparedStament执行dml
public class Demo4 {
public static void main(String[] args) {
Connection conn=null;
try {
conn=DBUtils.getConnection();
String sql
=“insert into dept values (?,?,?)”;
PreparedStatement ps
=conn.prepareStatement(sql);
//马上就会编译出一条sql的执行计划
ps.setInt(1, 3);
ps.setString(2, “wuber”);
ps.setString(3, “123”);
int n=ps.executeUpdate();
System.out.println(n);
// for (int i = 10; i < 20; i++) {
// ps.setInt(1, i);
// ps.setString(2,“test”+i);
// ps.setString(3, “123”);
// ps.executeUpdate();
// }
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DBUtils.closeConnection(conn);
}
}
}
使用PreparedStament指定dql
public class Demo5 {
public static void main(String[] args) {
String sql="select name from dept "
+ “where name like ?”;
Connection conn=null;
try {
conn=DBUtils.getConnection();
PreparedStatement ps =conn.prepareStatement(sql);
ps.setString(1, "%w%");
ResultSet rs=ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getString(1));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DBUtils.closeConnection(conn);
}
}
}
使用PreparedStatement批量处理
开发中不建议使用
public static void main(String[] args) {
Connection conn=null;
try {
conn=DBUtils.getConnection();
//先使用statement对象,创建10条记录
// Statement sta=conn.createStatement();
String sql
=“insert into dept values (?,?,?)”;
PreparedStatement ps=conn.prepareStatement(sql);
long start=System.currentTimeMillis();
for (int i = 2100; i < 3100; i++) {
ps.setInt(1, i);
ps.setString(2, "test"+i);
ps.setString(3, "123");
ps.executeUpdate();
}
// for (int i = 100; i < 1100; i++) {
// String sql=“insert into dept "
// + “values (”+i+”,"
// + “’”+“test”+i+"’,"
// + “‘123’)”;
System.out.println(sql);
// sta.executeUpdate(sql);
// }
long end=System.currentTimeMillis();
System.out.println(end-start);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DBUtils.closeConnection(conn);
}
}
演示sql注入
public class Demo7 {
public static void main(String[] args) {
//键盘输入
Scanner sc=new Scanner(System.in);
System.out.println(“请输入用户名”);
String name=sc.nextLine();
System.out.println(“请输入密码”);
String pwd=sc.nextLine();
boolean flag=login(name, pwd);
if (flag) {
System.out.println(“查有此人”);
}else{
System.out.println(“没有这个人”);
}
}
public static boolean login(String name,String pwd){
Connection conn=null;
try {
conn=DBUtils.getConnection();
Statement sta=conn.createStatement();
String sql=“select count(*) from dept "
+ “where name=’”+name+”’ "
+ “and password=’”+pwd+"’";
// System.out.println(sql);
ResultSet rs=sta.executeQuery(sql);
while (rs.next()) {
int n=rs.getInt(1);
if (n>=1) {
return true;
}else {
return false;
}
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.closeConnection(conn);
}
return false;
}
}
使用PreparedStatement预防sql注入
public class Demo8 {
public static void main(String[] args) {
//键盘输入
Scanner sc=new Scanner(System.in);
System.out.println(“请输入用户名”);
String name=sc.nextLine();
System.out.println(“请输入密码”);
String pwd=sc.nextLine();
boolean flag=login(name, pwd);
if (flag) {
System.out.println(“查有此人”);
}else{
System.out.println(“没有这个人”);
}
}
public static boolean login(String name,String pwd){
Connection conn=null;
try {
conn=DBUtils.getConnection();
String sql="select count(*) from dept "
+ “where name=? and password=?”;
PreparedStatement ps
=conn.prepareStatement(sql);
ps.setString(1, name);
ps.setString(2, pwd);
ResultSet rs=ps.executeQuery();
while(rs.next()){
int n=rs.getInt(1);
return n>=1;
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
DBUtils.closeConnection(conn);
}
return false;
}
}
JDBC-- day03
ResultSetMetaData
MetaData–元数据
是指结果集对象的相关其他数据,比如说总列数,每一列的名称,每一列的sql数据类型,每一列的java数据类型等
案例演示
public static void main(String[] args) {
Connection conn=null;
String sql="select id,name,password from dept where id<?";
try {
conn=DBUtiles.getConnection();
PreparedStatement ps=conn.prepareStatement(sql);
ps.setInt(1, 100);
ResultSet rs=ps.executeQuery();
//获取结果集的元数据
ResultSetMetaData meta=rs.getMetaData();
//遍历并显示结果集中所有列的名称
for (int i = 1; i <= meta.getColumnCount(); i++) {
System.out.println(meta.getColumnName(i));
}
//获得结果集的所有列数量
// int n=meta.getColumnCount();
// System.out.println(n);
//通过下标获得结果集的某一列的列名
// String name1=meta.getColumnName(1);
// String name2=meta.getColumnName(2);
// System.out.println(name1);
// System.out.println(name2);
// while (rs.next()) {
// System.out.println(rs.getString(“name”));
// }
//关闭结果集
rs.close();
//关闭PS对象
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DBUtiles.closeConnection(conn);
}
}
}
java中处理事务
由于jdbc自动提交事务,所以需要手动关闭
public class Demo2 {
/**
-
由于JDBC自动事务提交
-
1.关闭自动提交conn.setAutoCommit(false);
-
2.try的最后部分手动提交conn.commit();
-
3.由于一出现错误,就会运行catch块
-
在catch中conn.rollback();
-
4.封装回滚方法
-
@param args
*/
static String sql1=“update bal set”
+ " money=money+? where id=?";
static String sql2=“select money from bal where id=?”;public static void main(String[] args) {
pay(2, 4, 1000);
}
public static void pay(int from,int to,double money){
Connection conn=null;
try {
conn=DBUtiles.getConnection();
//关闭自动commit
conn.setAutoCommit(false);
PreparedStatement ps=conn.prepareStatement(sql1);
/**
* 业务模块—a–>b 打1000
* create table bal(
* id int,
* name varchar(20),
* money double(8,2)
* )
*/
//a-1000
ps.setDouble(1, -money);
ps.setInt(2, from);
int n=ps.executeUpdate();
//更新失败
if (n!=1) {
throw new Exception(“减钱失败”);
}
//b+1000
ps.setDouble(1, money);
ps.setInt(2, to);
n=ps.executeUpdate();
if (n!=1) {
throw new Exception(“加钱失败”);
}
ps.close();
//检查a有没有1000
ps=conn.prepareStatement(sql2);
ps.setInt(1, from);
ResultSet rs=ps.executeQuery();
while (rs.next()) {
double bal=rs.getDouble(1);
if (bal<0) {
throw new Exception(“余额不足”);
}
}
//手动提交
conn.commit();
} catch (Exception e) {
e.printStackTrace();
DBUtiles.rollback(conn);
}finally {
DBUtiles.closeConnection(conn);
}
}
}
批量更新
把多条sql存入Statement对象的缓存,一次性发送给DB执行
Statement sta=conn.createStatement();
// sta.addBatch(sql1);
// sta.addBatch(sql2);
// int[] arr=sta.executeBatch();
创建一个执行计划,把多条参数存入PreParedStatement对象缓存中,一次性发送给DB执行
PreparedStatement ps=conn.prepareStatement(sql6);
ps.setInt(1, 1);
ps.setString(2, “lili”);
ps.addBatch();
int[] arr=ps.executeBatch();
内存溢出OutOfMemory
获取自动生成的主键
String[] colNames={“id”};//自动生成值的列的列名
PreparedStatement ps =conn.prepareStatement(sql1, colNames); 必须与上面的命令一起使用 ps.getGeneratedKeys()
public static void main(String[] args) {
String sql1="create table "
+ "log1 (id int ,msg varchar(20))";
String sql2="create table "
+ "log2 (id int ,msg varchar(20))";
String sql3="create table "
+ "log3 (id int ,msg varchar(20))";
String sql4="create table "
+ "log4 (id int ,msg varchar(20))";
String sql5="create table "
+ "log5 (id int ,msg varchar(20))";
String sql6="insert into log1 values "
+ "(?,?)";
String sql7="insert into log2 values "
+ "(?,?)";
//获得连接对象
Connection conn=null;
try {
conn=DBUtiles.getConnection();
//批量更新的第一种方法
//把多个sql语句存入sta对象的缓存中
// Statement sta=conn.createStatement();
// sta.addBatch(sql1);
// sta.addBatch(sql2);
// sta.addBatch(sql3);
// sta.addBatch(sql4);
// sta.addBatch(sql5);
//一次性发送给数据库执行
//返回值 >=0—成功,有结果
//返回值-2----成功,没有结果
//返回值-3----不成功
// int[] arr=sta.executeBatch();
// System.out.println(Arrays.toString(arr));
//第二中批量更新的方式
//使用ps固定一个执行计划
//把一堆参数存入ps的缓存
//一次性发送给DB,进行处理
// PreparedStatement ps=conn.prepareStatement(sql6);
//
// ps.setInt(1, 1);
// ps.setString(2, “lili”);
// ps.addBatch();
//
// ps.setInt(1, 2);
// ps.setString(2, “lucy”);
// ps.addBatch();
//
// int[] arr=ps.executeBatch();
// System.out.println(Arrays.toString(arr));
PreparedStatement ps =conn.prepareStatement(sql7);
for (int i = 1; i < 100; i++) {
ps.setInt(1, i);
ps.setString(2, “test”+i);
ps.addBatch();
if (i%8==0) {//97.98.99
ps.executeBatch();
}
}
ps.executeBatch();
} catch (Exception e) {
e.printStackTrace();
}finally {
DBUtiles.closeConnection(conn);
}
}
DAO
DAO作为数据访问层,把业务逻辑层和数据库分割开来
业务逻辑层需要数据,就跟DAO要
业务逻辑层要保存数据,就交给DAO,让DAO去保存
业务逻辑层不关系数据如何获取,如何保存,全部都由DAO负责
public class UserDAO1 implements UserDAO{
private static final String search_by_id
="select * from user_1 where id=?";
private static final String search_all
="select * from user_1";
private static final String update_user_password
="update user_1 set name=?,password=? where id=?";
private static final String insert_user
="insert into user_1 values(null,?,?,?)";
public User findUserById(int id) {
Connection conn=null;
try {
conn=DBUtiles.getConnection();
PreparedStatement ps=conn.prepareStatement(search_by_id);
ps.setInt(1, id);
ResultSet rs=ps.executeQuery();
int i=0;
String name=null;
String pwd=null;
int age=0;
while(rs.next()){
i=rs.getInt(1);
name=rs.getString(2);
pwd=rs.getString(3);
age=rs.getInt(4);
}
return new User(i, name, pwd, age);
} catch (Exception e) {
e.printStackTrace();
DBUtiles.rollback(conn);
}finally{
DBUtiles.closeConnection(conn);
}
return null;
}
public List<User> findAllUser() {
Connection conn=null;
try {
conn=DBUtiles.getConnection();
conn.setAutoCommit(false);
Statement sta=conn.createStatement();
ResultSet rs=sta.executeQuery(search_all);
List<User> list=new ArrayList<User>();
while (rs.next()) {
int i=rs.getInt(1);
String name=rs.getString(2);
String pwd=rs.getString(3);
int age=rs.getInt(4);
User user=new User(i, name, pwd, age);
list.add(user);
}
conn.commit();
return list;
} catch (Exception e) {
e.printStackTrace();
DBUtiles.rollback(conn);
}finally {
DBUtiles.closeConnection(conn);
}
return null;
}
public int updateUser(User user) {
Connection conn=null;
try {
conn=DBUtiles.getConnection();
PreparedStatement ps=conn.prepareStatement(update_user_password);
ps.setString(1, user.getName());
ps.setString(2, user.getPwd());
ps.setInt(3, user.getId());
int n=ps.executeUpdate();
if (n!=1) {
throw new Exception("修改失败");
}
ps.close();
} catch (Exception e) {
e.printStackTrace();
DBUtiles.rollback(conn);
}finally {
DBUtiles.closeConnection(conn);
}
return 0;
}
public int saveUser(User user) {
Connection conn=null;
try {
conn=DBUtiles.getConnection();
PreparedStatement ps=conn.prepareStatement(insert_user);
ps.setString(1, user.getName());
ps.setString(2, user.getPwd());
ps.setInt(3, user.getAge());
int n=ps.executeUpdate();
if (n!=1) {
throw new Exception("插入失败");
}
ps.close();
} catch (Exception e) {
e.printStackTrace();
DBUtiles.rollback(conn);
}finally {
DBUtiles.closeConnection(conn);
}
return 0;
}
}
测试类
public static void main(String[] args) {
//数据--->数据库读取出来的
//jdbc-->conn-->ps--->rs-->user
// List<User> arr_user=UserDAO.getUserAll();
//用户填写,代码生成的---->保存到库中
//user-->DAO-->sql-->conn--->ps-->db
// User u1=new User(1, "lily", "123", 12);
// User u2=new User(2, "peipei", "123", 18);
// User u3=new User(3, "wangba", "123", 18);
// ArrayList<User> arr_user=new ArrayList<User>();
// arr_user.add(u1);
// arr_user.add(u2);
// arr_user.add(u3);
//验证dao的findUserById方法
UserDAO dao=new UserDAO1();
// User user=dao.findUserById(1);
// System.out.println(user.getName());
//验证dao的findall
// List<User> list=dao.findAllUser();
// for (int i = 0; i < list.size(); i++) {
// System.out.println(list.get(i).getId());
// }
//验证dao的修改方法
// User user=new User(2, "xiongda", "321", 18);
// dao.updateUser(user);
//验证dao的插入方法
User user=new User(1232, "xiongda", "321", 18);
dao.saveUser(user);
//登录验证功能
// Scanner sc=new Scanner(System.in);
// System.out.println("请输入您的用户名");
// String name=sc.nextLine();
// System.out.println("请输入您的密码");
// String pwd =sc.nextLine();
//
// for (int i = 0; i < arr_user.size(); i++) {
// if (name.equals(arr_user.get(i).getName())) {
//
// }
// }
}