1. 课程回顾
mysql加强
1)数据约束(表约束)
默认值: default 默认值
非空: not null
唯一: unique
主键: primary key (非空+唯一)
自增长: auto_increment
外键: foreign key 约束两种表 constraint 名 foreign key(column) references miantable(col)
2)关联查询(多表查询)
2.1 交叉连接(产生笛卡尔积:原因:连接条件不足够) 表数量-1
2.2 内连接查询: inner join
只有满足连接条件的数据才会显示!!!
2.3 左【外】连接查询:left [outer] join
左表的数据必须全部显示,用左表去匹配右表的数据,如果右表有符号条件的数据则显示符 合条件的数据;如果不符合条件,则显示null。
2.4 右【外】连接查询: right [outer] join
右表的数据必须全部显示,用右表去匹配左表的数据,如果左表有符号条件的数据则显示符 合条件的数据;如果不符合条件,则显示null。
2.5 自连接查询
3)存储过程
-- 创建存储过程语法
delimeter 结束符号
create procedure 名称(IN/OUT/INOUT 参数名称 参数类型)
begin
带逻辑的sql语句
end 结束符号
--调用存储过程
CALL 存储过程名称(实际参数);
今天的目标: jdbc基础
2 jdbc入门
2.1 之前操作数据
1)通过mysql的客户端工具,登录数据库服务器 (mysql -u root -p 密码)
2)编写sql语句
3)发送sql语句到数据库服务器执行
2.2 什么是jdbc?
使用java代码(程序)发送sql语句的技术,就是jdbc技术!!!!
2.3 使用jdbc发送sql前提
登录数据库服务器(连接数据库服务器)
数据库的IP地址
端口
数据库用户名
密码
当然使用mysql驱动之前,应当先导入,mysql提供的驱动jar包,官网下载,如果使用别的数据库oracle,sqlsever也要做类似的操作。
/** * jdbc连接数据库 * @author APPle * */ public class Demo1 { //连接数据库的URL private String url = "jdbc:mysql://localhost:3306/day17"; // jdbc协议:数据库子协议:主机:端口/连接的数据库 //
private String user = "root";//用户名 private String password = "root";//密码
/** * 第一种方法 * @throws Exception */ @Test public void test1() throws Exception{ //1.创建驱动程序类对象 Driver driver = new com.mysql.jdbc.Driver(); //新版本 //Driver driver = new org.gjt.mm.mysql.Driver(); //旧版本
//设置用户名和密码 Properties props = new Properties(); props.setProperty("user", user); props.setProperty("password", password);
//2.连接数据库,返回连接对象, 使用驱动进行连接 Connection conn = driver.connect(url, props);
System.out.println(conn); }
/** * 使用驱动管理器类连接数据库(注册了两次,没必要) * @throws Exception */ @Test public void test2() throws Exception{ Driver driver = new com.mysql.jdbc.Driver(); //Driver driver2 = new com.oracle.jdbc.Driver(); //1.注册驱动程序(可以注册多个驱动程序) DriverManager.registerDriver(driver); //DriverManager.registerDriver(driver2);
//2.连接到具体的数据库 Connection conn = DriverManager.getConnection(url, user, password); System.out.println(conn);
}
/** * (推荐使用这种方式连接数据库) * 推荐使用加载驱动程序类 来 注册驱动程序 * @throws Exception */ @Test public void test3() throws Exception{ //Driver driver = new com.mysql.jdbc.Driver();
//通过得到字节码对象的方式加载静态代码块,从而注册驱动程序 Class.forName("com.mysql.jdbc.Driver");
//Driver driver2 = new com.oracle.jdbc.Driver(); //1.注册驱动程序(可以注册多个驱动程序) //DriverManager.registerDriver(driver); //DriverManager.registerDriver(driver2);
//2.连接到具体的数据库 Connection conn = DriverManager.getConnection(url, user, password); System.out.println(conn);
}
} |
2.4 JDBC接口核心的API
java.sql.* 和 javax.sql.*
|- Driver接口: 表示java驱动程序接口。所有的具体的数据库厂商要来实现此接口。
|- connect(url, properties): 连接数据库的方法。(法一)
url: 连接数据库的URL
URL语法: jdbc协议:数据库子协议://主机:端口/数据库
user: 数据库的用户名
password: 数据库用户密码
|- DriverManager类: 驱动管理器类,用于管理所有注册的驱动程序 (法二,法三)
|-registerDriver(driver) : 注册驱动类对象
|-Connection getConnection(url,user,password); 获取连接对象
|- Connection接口: 表示java程序和数据库的连接对象。
|- Statement createStatement() : 创建Statement对象
|- PreparedStatement prepareStatement(String sql) 创建PreparedStatement对象
|- CallableStatement prepareCall(String sql) 创建CallableStatement对象
|- Statement接口: 用于执行静态的sql语句
|- int executeUpdate(String sql) : 执行静态的更新sql语句(DDL,DML)
|- ResultSet executeQuery(String sql) :执行的静态的查询sql语句(DQL)
|-PreparedStatement接口:用于执行预编译sql语句
|- int executeUpdate() : 执行预编译的更新sql语句(DDL,DML)
|-ResultSet executeQuery() : 执行预编译的查询sql语句(DQL)
|-CallableStatement接口:用于执行存储过程的sql语句(call xxx)
|-ResultSet executeQuery() : 调用存储过程的方法
|- ResultSet接口:用于封装查询出来的数据
|- boolean next() : 将光标移动到下一行
|-getXX() : 获取列的值
3 使用Statement执行sql语句
3.1 执行DDL语句
/** * 执行DDL语句(创建表) */ @Test public void test1(){ Statement stmt = null; Connection conn = null; try { //1.驱动注册程序 Class.forName("com.mysql.jdbc.Driver");
//2.获取连接对象 conn = DriverManager.getConnection(url, user, password);
//3.创建Statement stmt = conn.createStatement();
//4.准备sql String sql = "CREATE TABLE student(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),gender VARCHAR(2))";
//5.发送sql语句,执行sql语句,得到返回结果 int count = stmt.executeUpdate(sql);
//6.输出 System.out.println("影响了"+count+"行!"); } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e); } finally{ //7.关闭连接(顺序:后打开的先关闭) if(stmt!=null) try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } if(conn!=null) try { conn.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } } |
3.2 执行DML语句
/** * 使用Statement执行DML语句 * @author APPle * */ public class Demo2 { private String url = "jdbc:mysql://localhost:3306/day17"; private String user = "root"; private String password = "root";
/** * 增加 */ @Test public void testInsert(){ Connection conn = null; Statement stmt = null; try { //通过工具类获取连接对象 conn = JdbcUtil.getConnection();
//3.创建Statement对象 stmt = conn.createStatement();
//4.sql语句 String sql = "INSERT INTO student(NAME,gender) VALUES('李四','女')";
//5.执行sql int count = stmt.executeUpdate(sql);
System.out.println("影响了"+count+"行");
} catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e); } finally{ //关闭资源 /*if(stmt!=null) try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } if(conn!=null) try { conn.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); }*/ JdbcUtil.close(conn, stmt); } }
/** * 修改 */ @Test public void testUpdate(){ Connection conn = null; Statement stmt = null; //模拟用户输入 String name = "陈六"; int id = 3; try { /*//1.注册驱动 Class.forName("com.mysql.jdbc.Driver");
//2.获取连接对象 conn = DriverManager.getConnection(url, user, password);*/ //通过工具类获取连接对象 conn = JdbcUtil.getConnection();
//3.创建Statement对象 stmt = conn.createStatement();
//4.sql语句 String sql = "UPDATE student SET NAME='"+name+"' WHERE id="+id+"";
System.out.println(sql);
//5.执行sql int count = stmt.executeUpdate(sql);
System.out.println("影响了"+count+"行");
} catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e); } finally{ //关闭资源 /*if(stmt!=null) try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } if(conn!=null) try { conn.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); }*/ JdbcUtil.close(conn, stmt); } }
/** * 删除 */ @Test public void testDelete(){ Connection conn = null; Statement stmt = null; //模拟用户输入 int id = 3; try { /*//1.注册驱动 Class.forName("com.mysql.jdbc.Driver");
//2.获取连接对象 conn = DriverManager.getConnection(url, user, password);*/ //通过工具类获取连接对象 conn = JdbcUtil.getConnection();
//3.创建Statement对象 stmt = conn.createStatement();
//4.sql语句 String sql = "DELETE FROM student WHERE id="+id+"";
System.out.println(sql);
//5.执行sql int count = stmt.executeUpdate(sql);
System.out.println("影响了"+count+"行");
} catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e); } finally{ //关闭资源 /*if(stmt!=null) try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } if(conn!=null) try { conn.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); }*/ JdbcUtil.close(conn, stmt); } } } |
上面重复的代码块都是对数据库的操作,这里抽取出来放到了一个工具类中,JdbcUtil.java
package gz.itcast.util;
import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties;
/** * jdbc宸ュ叿绫� * @author APPle * */ public class JdbcUtil { private static String url = null; private static String user = null; private static String password = null; private static String driverClass = null;
/** * 闈欐�浠g爜鍧椾腑锛堝彧鍔犺浇涓�锛� */ static{ try { //璇诲彇db.properties鏂囦欢 Properties props = new Properties(); /** * . 浠h〃java鍛戒护杩愯鐨勭洰褰� * 鍦╦ava椤圭洰涓嬶紝. java鍛戒护鐨勮繍琛岀洰褰曚粠椤圭洰鐨勬牴鐩綍寮� * 鍦╳eb椤圭洰涓嬶紝 . java鍛戒护鐨勮�杩愯鐩綍浠巘omcat/bin鐩綍寮� * 鎵�互涓嶈兘浣跨敤鐐� */ //FileInputStream in = new FileInputStream("./src/db.properties");
/** * 浣跨敤绫昏矾寰勭殑璇诲彇鏂瑰紡 * / : 鏂滄潬琛ㄧずclasspath鐨勬牴鐩綍 * 鍦╦ava椤圭洰涓嬶紝classpath鐨勬牴鐩綍浠巄in鐩綍寮� * 鍦╳eb椤圭洰涓嬶紝classpath鐨勬牴鐩綍浠嶹EB-INF/classes鐩綍寮� */ InputStream in = JdbcUtil.class.getResourceAsStream("/db.properties");
//鍔犺浇鏂囦欢 props.load(in); //璇诲彇淇℃伅 url = props.getProperty("url"); user = props.getProperty("user"); password = props.getProperty("password"); driverClass = props.getProperty("driverClass");
//娉ㄥ唽椹卞姩绋嬪簭 Class.forName(driverClass); } catch (Exception e) { e.printStackTrace(); System.out.println("椹辩▼绋嬪簭娉ㄥ唽鍑洪敊"); } }
/** * 鎶藉彇鑾峰彇杩炴帴瀵硅薄鐨勬柟娉� */ public static Connection getConnection(){ try { Connection conn = DriverManager.getConnection(url, user, password); return conn; } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } }
/** * 閲婃斁璧勬簮鐨勬柟娉� */ public static void close(Connection conn,Statement stmt){ if(stmt!=null){ try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } if(conn!=null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } }
public static void close(Connection conn,Statement stmt,ResultSet rs){ if(rs!=null) try { rs.close(); } catch (SQLException e1) { e1.printStackTrace(); throw new RuntimeException(e1); } if(stmt!=null){ try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } if(conn!=null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } } }
|
3.3 执行DQL语句
/** * 使用Statement执行DQL语句(查询操作) * @author APPle */ public class Demo3 {
@Test public void test1(){ Connection conn = null; Statement stmt = null; try{ //获取连接 conn = JdbcUtil.getConnection(); //创建Statement stmt = conn.createStatement(); //准备sql String sql = "SELECT * FROM student"; //执行sql ResultSet rs = stmt.executeQuery(sql);
//移动光标 /*boolean flag = rs.next();
flag = rs.next(); flag = rs.next(); if(flag){ //取出列值 //索引 int id = rs.getInt(1); String name = rs.getString(2); String gender = rs.getString(3); System.out.println(id+","+name+","+gender);
//列名称 int id = rs.getInt("id"); String name = rs.getString("name"); String gender = rs.getString("gender"); System.out.println(id+","+name+","+gender); }*/
//遍历结果 while(rs.next()){ int id = rs.getInt("id"); String name = rs.getString("name"); String gender = rs.getString("gender"); System.out.println(id+","+name+","+gender); }
}catch(Exception e){ e.printStackTrace(); throw new RuntimeException(e); }finally{ JdbcUtil.close(conn, stmt); } } } |
4 使用PreparedStatement执行sql语句
public class Demo1 {
/** * 增加 */ @Test public void testInsert() { Connection conn = null; PreparedStatement stmt = null; try { //1.获取连接 conn = JdbcUtil.getConnection();
//2.准备预编译的sql String sql = "INSERT INTO student(NAME,gender) VALUES(?,?)"; //?表示一个参数的占位符
//3.执行预编译sql语句(检查语法) stmt = conn.prepareStatement(sql);
//4.设置参数值 /** * 参数一: 参数位置 从1开始 */ stmt.setString(1, "李四"); stmt.setString(2, "男");
//5.发送参数,执行sql int count = stmt.executeUpdate();
System.out.println("影响了"+count+"行");
} catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e); } finally { JdbcUtil.close(conn, stmt); } }
/** * 修改 */ @Test public void testUpdate() { Connection conn = null; PreparedStatement stmt = null; try { //1.获取连接 conn = JdbcUtil.getConnection();
//2.准备预编译的sql String sql = "UPDATE student SET NAME=? WHERE id=?"; //?表示一个参数的占位符
//3.执行预编译sql语句(检查语法) stmt = conn.prepareStatement(sql);
//4.设置参数值 /** * 参数一: 参数位置 从1开始 */ stmt.setString(1, "王五"); stmt.setInt(2, 9);
//5.发送参数,执行sql int count = stmt.executeUpdate();
System.out.println("影响了"+count+"行");
} catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e); } finally { JdbcUtil.close(conn, stmt); } }
/** * 删除 */ @Test public void testDelete() { Connection conn = null; PreparedStatement stmt = null; try { //1.获取连接 conn = JdbcUtil.getConnection();
//2.准备预编译的sql String sql = "DELETE FROM student WHERE id=?"; //?表示一个参数的占位符
//3.执行预编译sql语句(检查语法) stmt = conn.prepareStatement(sql);
//4.设置参数值 /** * 参数一: 参数位置 从1开始 */ stmt.setInt(1, 9);
//5.发送参数,执行sql int count = stmt.executeUpdate();
System.out.println("影响了"+count+"行");
} catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e); } finally { JdbcUtil.close(conn, stmt); } }
/** * 查询 */ @Test public void testQuery() { Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; try { //1.获取连接 conn = JdbcUtil.getConnection();
//2.准备预编译的sql String sql = "SELECT * FROM student";
//3.预编译 stmt = conn.prepareStatement(sql);
//4.执行sql rs = stmt.executeQuery();
//5.遍历rs while(rs.next()){ int id = rs.getInt("id"); String name = rs.getString("name"); String gender = rs.getString("gender"); System.out.println(id+","+name+","+gender); }
} catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e); } finally { //关闭资源 JdbcUtil.close(conn,stmt,rs); } } } |
PreparedStatement vs Statment
1)语法不同:PreparedStatement可以使用预编译的sql,而Statment只能使用静态的sql
2)效率不同: PreparedStatement可以使用sql缓存区,效率比Statment高
3)安全性不同: PreparedStatement可以有效防止sql注入,而Statment不能防止sql注入。
安全性,举了一个模拟登陆的例子。Statement存在sql注入的风险,PreparedStatement可以防止这种sql注入的风险
推荐使用PreparedStatement,可以有效的防止sql被注入的风险。
5 CallableStatement执行存储过程
/** * 使用CablleStatement调用存储过程 * @author APPle * */ public class Demo1 {
/** * 调用带有输入参数的存储过程 * CALL pro_findById(4); */ @Test public void test1(){ Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; try { //获取连接 conn = JdbcUtil.getConnection();
//准备sql String sql = "CALL pro_findById(?)"; //可以执行预编译的sql
//预编译 stmt = conn.prepareCall(sql);
//设置输入参数 stmt.setInt(1, 6);
//发送参数 rs = stmt.executeQuery(); //注意: 所有调用存储过程的sql语句都是使用executeQuery方法执行!!!
//遍历结果 while(rs.next()){ int id = rs.getInt("id"); String name = rs.getString("name"); String gender = rs.getString("gender"); System.out.println(id+","+name+","+gender); }
} catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e); } finally { JdbcUtil.close(conn, stmt ,rs); } }
/** * 执行带有输出参数的存储过程 * CALL pro_findById2(5,@NAME); */ @Test public void test2(){ Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; try { //获取连接 conn = JdbcUtil.getConnection(); //准备sql String sql = "CALL pro_findById2(?,?)"; //第一个?是输入参数,第二个?是输出参数
//预编译 stmt = conn.prepareCall(sql);
//设置输入参数 stmt.setInt(1, 6); //设置输出参数(注册输出参数) /** * 参数一: 参数位置 * 参数二: 存储过程中的输出参数的jdbc类型 VARCHAR(20) */ stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
//发送参数,执行 stmt.executeQuery(); //结果不是返回到结果集中,而是返回到输出参数中
//得到输出参数的值 /** * 索引值: 预编译sql中的输出参数的位置 */ String result = stmt.getString(2); //getXX方法专门用于获取存储过程中的输出参数
System.out.println(result);
} catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e); } finally { JdbcUtil.close(conn, stmt ,rs); } } } |
Jdbcutil工具类的进一步的优化
无论做任何的数据库操作(curd)之前都需要先链接数据库,操作完成之后,要及时的释放链接的资源,所以这一块内容是相同一般就在项目中写一个JdbcUtil的工具类。
链接数据库的时候,url,user,password,diverCclass 这些不应当写死,应当放在一个配置文件中,然后,每次从配置文件中读取,那么关键,就是从什么样的路径中读取,才能保证自己写的JdbcUtil是工具类,在java项目和web项目中保持通用。这里就是一个在java基础版加强中提到的一个问题“类路径”。
package gz.itcast.jdbcutil;
import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties;
/** * jdbc工具类 * @author APPle * */ public class JdbcUtil { private static String url = null; private static String user = null; private static String password = null; private static String driverClass = null;
/** * 静态代码块中(只加载一次) */ static{ try { //读取db.properties文件 Properties props = new Properties(); /** * . 代表java命令运行的目录 * 在java项目下,. java命令的运行目录从项目的根目录开始 * 在web项目下, . java命令的而运行目录从tomcat/bin目录开始 * 所以不能使用点. */ //FileInputStream in = new FileInputStream("./src/db.properties");
/** * 使用类路径的读取方式 * / : 斜杠表示classpath的根目录 * 在java项目下,classpath的根目录从bin目录开始 * 在web项目下,classpath的根目录从WEB-INF/classes目录开始,所以,在web项目中db.properties文件应当放到classes文件夹下 */ InputStream in = JdbcUtil.class.getResourceAsStream("/db.properties");
//加载文件 props.load(in); //读取信息 url = props.getProperty("url"); user = props.getProperty("user"); password = props.getProperty("password"); driverClass = props.getProperty("driverClass");
//注册驱动程序 Class.forName(driverClass); } catch (Exception e) { e.printStackTrace(); System.out.println("驱程程序注册出错"); } }
/** * 抽取获取连接对象的方法 */ public static Connection getConnection(){ try { Connection conn = DriverManager.getConnection(url, user, password); return conn; } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } }
/** * 释放资源的方法 */ public static void close(Connection conn,Statement stmt){ if(stmt!=null){ try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } if(conn!=null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } }
public static void close(Connection conn,Statement stmt,ResultSet rs){ if(rs!=null) try { rs.close(); } catch (SQLException e1) { e1.printStackTrace(); throw new RuntimeException(e1); } if(stmt!=null){ try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } if(conn!=null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } } }
|
在此回顾web项目的分包,这是一个myeclipse中可以看到的一个比较清晰的本地包结构
Entry :实体包一般遵循javabean的规范,写的一些类实体。
Dao接口 :直接和数据库打交道。
|-Daoimpl dao的实现分不同,看你要访问的数据时在哪里,有可能是文件读写dao,或者是xml的dao,或者 数据库dao
Service接口:处在dao和servlet之间,在中间连接dao层和servlet层,这一层其实是dao层的翻版映射
|-serviceimpl service的实现,其实是daoimpl的映射,其中就是对daoimpl的调用
Servlet :主要负责在服务器端的逻辑处理,其实就是实现功能流转,页面的跳转,别的不干什么事情。直接和service 打交道,在这就是跳转到不同的jsp或者html页面
Util包 这个包里面是项目用到的一些工具类
Test 测试类,主要用于测试
Exception 自定义的一些异常
回顾一下服务器端的包结构:
在我们开发的时候,我们需要用的文件,一般就放到/src下面,比如db.properties文件,当我们部署项目到服务器的时候,文件会自动的拷贝到,服务器端的webapps/inf/classes目录下。项目中要用的外部包,我们应当放到webroot/web-inf/lib的下面,拷贝之后,我们在myeclipse下面开发的,时候,对应的包会自然拷贝到上面的eclipse的一个外部包目录中,比如在服务器端要用的dom4j等。