回顾
表之间的关系:
一对多|多对一: 在多的一方添加一列,并且添加外键约束,指向一的一方
多对多 : 创建一张中间表,将多对多的关系拆成两个一对多的关系,中间表至少要包含两列,分别指向原来的两张表的主键
一对一:
1.将两张表合成一张表
2.当作一对多的情况处理,增加一列,并且添加外键约束和唯一约束,指向另外一张表
3.将两张表的主键填写成一样
多表查询:
内连接:
select * from product p,category c where p.cno=c.cid;
select * from product p inner join category c on p.cno=c.cid
左外连接:
select * from product p left outer join category c on p.cno=c.cid;
右外连接:
select * from product p right outer join category c on p.cno=c.cid;
子查询: 查询语句中嵌套查询语句
使用java操作数据库表中数据的增删改查
一、JDBC&连接池
1. jdbc介绍
JDBC(Java DataBase Connectivity ,java数据库连接)是一种用于执行SQL语句的Java API,可以为多种关系数据库提供统一访问,它由一组用Java语言编写的类和接口组成。JDBC提供了一种基准,据此可以构建更高级的工具和接口,使数据库开发人员能够编写数据库应用程序。 简言之: 通过jdbc, 可以使用java语言来操作数据库
自从Java语言于1995年5月正式公布以来,Java风靡全球。出现大量的用java语言编写的程序,其中也包括数据库应用程序。由于没有一个Java语言的API,编程人员不得不在Java程序中加入C语言的ODBC函数调用。这就使很多Java的优秀特性无法充分发挥,比如平台无关性、面向对象特性等。随着越来越多的编程人员对Java语言的日益喜爱,越来越多的公司在Java程序开发上投入的精力日益增加,对java语言接口的访问数据库的API的要求越来越强烈。也由于ODBC的有其不足之处,比如它并不容易使用,没有面向对象的特性等等,SUN公司决定开发一Java语言为接口的数据库应用程序开发接口。在JDK1.x版本中,JDBC只是一个可选部件,到了JDK1.1公布时,SQL类包(也就是JDBCAPI)就成为Java语言的标准部件。
2. jdbc入门
- 添加
mysql 驱动
compile group: 'mysql', name: 'mysql-connector-java', version: '5.1.17'
- 入门代码
//1. 注册驱动
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//2. 建立连接
//DriverManager.getConnection("jdbc:mysql://localhost/test?user=monty&password=greatsqldb");
//2. 建立连接 参数一: 协议 + 访问的数据库 , 参数二: 用户名 , 参数三: 密码。
conn = DriverManager.getConnection("jdbc:mysql://localhost/student", "root", "root");
//3. 创建statement , 跟数据库打交道,一定需要这个对象
st = conn.createStatement();
//4. 执行查询 , 得到结果集
String sql = "select * from t_stu";
rs = st.executeQuery(sql);
//5. 遍历查询每一条记录
while(rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
System.out.println("id="+id + "===name="+name+"==age="+age);
}
//6. 关闭连接,释放资源
if (rs != null) {
try {
rs.close();
} catch (SQLException sqlEx) { } // ignore
rs = null;
}
...
3. 注册驱动小细节
在我们通过DriverManager 去获取连接对象的时候,使用了一行代码
DriverManager.registerDriver(...)
来注册驱动,只有注册驱动,我们才能获取连接对象。但是这行注册驱动的背后,有些细节,在这里给大家说一说。
在com.mysql.jdbc.Driver类种有一段静态代码 , 只要这个Driver被加载到jvm中,那么就会注册驱动。所以注册驱动的时候,可以使用文档中的写法。 Class.forName(“com.mysql.jdbc.Driver”);
static {
try {
DriverManager.registerDriver(new Driver());
} catch (SQLException var1) {
throw new RuntimeException("Can't register driver!");
}
}
4. jdbc工具类抽取
1. 基本抽取
String driverClass = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql:///heima02";
String name = "root";
String password= "root";
/**
* 获取连接对象
* @return
*/
public static Connection getConn(){
Connection conn = null;
try {
Class.forName(driverClass);
//2. 建立连接 参数一: 协议 + 访问的数据库 , 参数二: 用户名 , 参数三: 密码。
conn = DriverManager.getConnection(url, name, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
/**
* 释放资源
* @param conn
* @param st
* @param rs
*/
public static void release(Connection conn , Statement st , ResultSet rs){
closeRs(rs);
closeSt(st);
closeConn(conn);
}
private static void closeRs(ResultSet rs){
try {
if(rs != null){
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
rs = null;
}
}
private static void closeSt(Statement st){
try {
if(st != null){
st.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
st = null;
}
}
private static void closeConn(Connection conn){
try {
if(conn != null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
conn = null;
}
}
2. properties使用
即便上面抽取出来了工具类,但是对于数据库连接的配置,我们仍然是在代码里面进行硬编码,一旦我们想要修改数据库的连接信息,那么操作起来就显得不是那么方便。所以通常会采用配置文件,在外部声明数据库连接信息,在代码里面读取配置。 这些配置信息,未来大家会见到
properties
|xml
两种形态的情景
- 在resource下新建一个properties文件, 名称随意
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost/heima02
name=root
password=root
- 工具类种读取配置文件内容
static{
try {
//1. 创建一个属性配置对象
Properties properties = new Properties();
//使用类加载器,去读取src底下的资源文件。 后面在servlet
InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
//导入输入流。
properties.load(is);
//读取属性
driverClass = properties.getProperty("driverClass");
url = properties.getProperty("url");
name = properties.getProperty("name");
password = properties.getProperty("password");
} catch (Exception e) {
e.printStackTrace();
}
}
4. 细节处理
其实可以省略掉
public class JDBCUtil {
static String driverClass = null;
static String url = null;
static String name = null;
static String password= null;
static{
try {
//1. 创建一个属性配置对象
Properties properties = new Properties();
InputStream is = new FileInputStream("jdbc.properties");
//使用类加载器,去读取src底下的资源文件。 后面在servlet
// InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
//导入输入流。
properties.load(is);
//读取属性
driverClass = properties.getProperty("driverClass");
url = properties.getProperty("url");
name = properties.getProperty("name");
password = properties.getProperty("password");
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 获取连接对象
* @return
*/
public static Connection getConn(){
Connection conn = null;
try {
Class.forName(driverClass);
//静态代码块 ---> 类加载了,就执行。 java.sql.DriverManager.registerDriver(new Driver());
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//DriverManager.getConnection("jdbc:mysql://localhost/test?user=monty&password=greatsqldb");
//2. 建立连接 参数一: 协议 + 访问的数据库 , 参数二: 用户名 , 参数三: 密码。
conn = DriverManager.getConnection(url, name, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
/**
* 释放资源
* @param conn
* @param st
* @param rs
*/
public static void release(Connection conn , Statement st , ResultSet rs){
closeRs(rs);
closeSt(st);
closeConn(conn);
}
private static void closeRs(ResultSet rs){
try {
if(rs != null){
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
rs = null;
}
}
private static void closeSt(Statement st){
try {
if(st != null){
st.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
st = null;
}
}
private static void closeConn(Connection conn){
try {
if(conn != null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
conn = null;
}
}
}
5. jdbc crud
1. sql语句回顾
- 添加
insert into student values(null , 'zhangsan' , 18);
- 删除
delete from student where id = 5;
- 修改
UPDATE student SET name = '奥巴马' WHERE id = 3;
- 查询
select * from student;
2. 添加
@Test
public void testSave(){
Connection conn = null;
Statement statement = null;
try {
//1. 获取连接对象
conn = JDBCUtil.getConn();
//2. 准备语句
statement = conn.createStatement();
//3. 执行语句
String sql = "insert into student values(null , 'zhangsansan',29)";
int result = statement.executeUpdate(sql);
System.out.println("result=" + result);
} catch (SQLException e) {
e.printStackTrace();
}finally {
//4. 释放资源
JDBCUtil.release(conn , statement , null);
}
}
3. 删除
@Test
public void testDelete(){
Connection conn = null;
Statement statement = null;
try {
//1. 获取连接对象
conn = JDBCUtil.getConn();
//2. 准备语句
statement = conn.createStatement();
//3. 执行语句
String sql = "delete from student where id = 4";
int result = statement.executeUpdate(sql);
System.out.println("result=" + result);
} catch (SQLException e) {
e.printStackTrace();
}finally {
//4. 释放资源
JDBCUtil.release(conn , statement , null);
}
}
4. 修改
@Test
public void testUpdate(){
Connection conn = null;
Statement statement = null;
try {
//1. 获取连接对象
conn = JDBCUtil.getConn();
//2. 准备语句
statement = conn.createStatement();
//3. 执行语句
String sql = "update student set age = 88 where id = 5";
int result = statement.executeUpdate(sql);
System.out.println("result=" + result);
} catch (SQLException e) {
e.printStackTrace();
}finally {
//4. 释放资源
JDBCUtil.release(conn , statement , null);
}
}
5. 查询
@Test
public void testFindAll(){
Connection conn = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//1. 获取连接对象
conn = JDBCUtil.getConn();
//2. 准备语句
statement = conn.createStatement();
//3. 执行语句
String sql = "select * from student";
resultSet = statement.executeQuery(sql);
//4. 遍历查询结果
while(resultSet.next()){
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
System.out.println(id + " : " + name + " : " + age);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//4. 释放资源
JDBCUtil.release(conn , statement , resultSet);
}
}
6. Statement 注入问题
statement 现在已经很少直接使用了,反而是使用PrepareStatement 来替代它。从关系上来看,PrepareStatement 是 Statement的子接口,作为它的一个扩展。一般企业级应用开发都会采用PrepareStatement , 之所以这么做,是处于以下几个原因考虑:
- PreparedStatement 可以写动态参数化的查询
- PreparedStatement 最重要的一点好处是它拥有更佳的性能优势,SQL语句会预编译在数据库系统中
- PreparedStatement 可以防止SQL注入式攻击
- 比起Statement 凌乱的字符串追加似的查询,PreparedStatement查询可读性更好、更安全。
//基本的登录操作
strSQL = "SELECT * FROM users WHERE name = '" + userName + "' and pw = '"+ passWord +"';"
//如果登录的时候,恶意写成
userName = "1 OR 1=1";
passWord = "1 OR 1=1";
//那么最终的登录语句就会变成这样:
strSQL = "SELECT * FROM users WHERE name = 1 OR 1=1 and pw = 1 OR 1=1"
上面的语句只是最终的呈现结果。 在输入用户名和密码的时候,要写这样:
username : 1 'or' 1=1
password : 1 'or' 1=1
7. PrepareStatement CRUD
该小节其实就是把Statement的代码重新修正一下。
@Test
public void testSave(){
Connection conn = null;
PreparedStatement ps = null;
try {
//1. 获取连接对象
conn = JdbcUtil02.getConn();
//Statement st = conn.createStatement();
//st.execute("insert into student values (null , 'wangwu',18)");
//2. 创建ps对象
String sql = "insert into student values(null , ? , ?)";
ps = conn.prepareStatement(sql);
//3. 填充占位符
ps.setString( 1, "王五");
ps.setInt(2 , 28);
//4. 执行Statement
int result = ps.executeUpdate();
System.out.println("result=" + result);
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtil02.release(conn , ps , null);
}
}
6.Dao 模式
在早前第一天的时候,就给大家讲过了三层架构的概念。实际上有关数据操作的逻辑都应该是位于数据访问这一层。
DAO
: 全称是data access object,数据库访问对象,主要的功能就是用于进行数据操作的,在程序的标准开发架构中属于数据层的操作 。 所以接下来我们会把让代码变得更丰富一些,使用dao的模式来封装数据库操作代码。未来为了让代码更易于管理,条理更清晰,通常都会采用分层的形式来包装代码
- dao 接口
public interface StudentDao {
void save(User user);
}
- dao 实现
public class StudentDaoImpl implements StudentDao {
private static final String TAG = "StudentDaoImpl";
@Override
public void save(User user) {
Connection conn = null;
Statement statement = null;
try {
//1. 获取连接对象
conn = JDBCUtil.getConn();
//2. 准备语句
statement = conn.createStatement();
//3. 执行语句
String sql = "insert into student values(null , 'zhangsansan',29)";
int result = statement.executeUpdate(sql);
System.out.println("result=" + result);
} catch (SQLException e) {
e.printStackTrace();
}finally {
//4. 释放资源
JDBCUtil.release(conn , statement , null);
}
}
}
为什么service 和 dao都需要写接口呢?
这是一种编程思想, 多态的思想。
接口的作用是用于声明功能,定义标准、规范。 实现类必须在接口规范下实现逻辑。
a。 我不用接口,我就是一个类,但是我也很规范,很标准。
接口声明规范,实现类做具体的实现,那么就是声明和实现分开了。
面向接口编程
如果没有接口,就一个纯粹的具体类。 张三做了A模块,正好要调用李四的B模块的某个方法。
StudentDao dao = new StudentDaoImpl();
- 接口的作用是用于声明功能,定义标准、规范。 实现类必须在接口规范下实现逻辑。
- 接口的好处是在协作时,大家可以面向接口编程,无需关心具体是如何实现的。
- 接口的声明,如果不够优秀,可以在后期扩展子接口,这可以体现迭代更新的记录。思考的例子就是: Statement 和 PrepareStatement
7. 连接池
1. 介绍
数据库连接是一种关键的、有限的、昂贵的资源,这一点在多用户的网页应用程序中体现得尤为突出。对数据库连接的管理能显著影响到整个应用程序的伸缩性和健壮性,影响到程序的性能指标。数据库连接池正是针对这个问题提出来的
连接池基本的思想是在系统初始化的时候,将数据库连接作为对象存储在内存中,当用户需要访问数据库时,并非建立一个新的连接,而是从连接池中取出一个已建立的空闲连接对象。使用完毕后,用户也并非将连接关闭,而是将连接放回连接池中,以供下一个请求访问使用。而连接的建立、断开都由连接池自身来管理。同时,还可以通过设置连接池的参数来控制连接池中的初始连接数、连接的上下限数以及每个连接的最大使用次数、最大空闲时间等等。也可以通过其自身的管理机制来监视数据库连接的数量、使用情况等
2. 常见的连接池
DBCP | c3p0 | hikari CP | Druid
3. C3P0
//添加依赖
compile group: 'com.mchange', name: 'c3p0', version: '0.9.5.2'
- 代码版本
@Test
public void testC3p0(){
try {
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setDriverClass("com.mysql.jdbc.Driver");
dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/heima02");
dataSource.setUser("root");
dataSource.setPassword("root");
//设置初始化连接数
dataSource.setInitialPoolSize(5);
//设置最大连接数
dataSource.setMaxPoolSize(10);
//获取连接
Connection conn = dataSource.getConnection();
//操作数据库
//...
//3. 执行语句
String sql = "insert into student values(null ,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,"lisi");
ps.setInt(2,19);
ps.executeUpdate();
//释放资源..回收连接对象
ps.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
- 配置版本
一般连接池的配置都是采用配置文件来声明,很少有使用代码来配置连接池的。c3p0的配置文件形式可以选择使用xml 或者是用 properties形态。
- 在resource下新建一个xml文件,名称为 c3p0-config.xml 内容如下:
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///heima02</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>
</c3p0-config>
- 代码
@Test
public void testC3p0(){
try {
//默认会读取xml文件内容。 所以xml的名字必须固定。
ComboPooledDataSource dataSource = new ComboPooledDataSource();
//获取连接
Connection conn = dataSource.getConnection();
//操作数据库
//...
//3. 执行语句
String sql = "insert into student values(null ,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,"lisi2");
ps.setInt(2,19);
ps.executeUpdate();
//释放资源..回收连接对象
ps.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
8. DBUtils
dbutils 是 Apache 开发的一套封装了jdbc操作的jar包。使用它,我们可以不用再声明以前那些的对象了。它的主要操作方法就两个:
update
和query
, update方法针对 增删改 , query方法针对查询。
- 导入约束
compile group: 'commons-dbutils', name: 'commons-dbutils', version: '1.6'
- 增加
@Override
public void insert(Student student) throws SQLException {
QueryRunner runner = new QueryRunner(JDBCUtil02.getDataSource());
runner.update("insert into stu values(null , ?,?,?)" ,
student.getSname(),
student.getGender(),
student.getPhone());
}
- 删除
@Override
public void delete(int sid) throws SQLException {
QueryRunner runner = new QueryRunner(JDBCUtil02.getDataSource());
runner.update("delete from stu where sid=?" ,sid);
}
- 更新
@Override
public void update(Student student) throws SQLException {
QueryRunner runner = new QueryRunner(JDBCUtil02.getDataSource());
runner.update("update stu set sname=? , gender=? , phone=? ",
student.getSname(),
student.getGender(),
student.getPhone();
}
- 查询
@Override
public List<Student> findAll() throws SQLException {
QueryRunner runner = new QueryRunner(JDBCUtil02.getDataSource());
return runner.query("select * from stu", new BeanListHandler<Student>(Student.class));
}
总结:
JDBC: JAVA DATABASE CONNECTIVITY java 数据库连接
为什么要学习jdbc?
因为不同的数据库,它的操作方式/方式是不一样,我们希望有一个统一的接口
mysql -uroot -proot , sqlplus 用户名/密码
JDBC开发步骤:
1.注册驱动: class.forName(驱动类的全路径)
2.获取连接: DriverManager.getConnection(url,username,password)
3.获取执行SQL的对象: connection.createStatement(),connection.prepareStatement()
statement : 会存在Sql注入的问题
prepareStatement : 不会出现, insert into user values(?,?,?)
setXXX 设置参数,参数的起始角标是从1开始
4.执行SQL
executeUpdate :增删改的操作,返回影响行数
executeQuery: 执行查询的操作,ResultSet
5. 释放资源
以后将来我们使用的都是PrepareStatement
Dao模式? 降低代码耦合
IUserDao --- 定义/约束方法名称 ---> UserDaoImpl
连接池: 为了提高效率,程序一启动,就准备好若干个连接,当需要使用的时候,直接从池子中去获取
C3P0:
c3p0-config.xml 名称必须是固定,必须放在resources下面
new ComboPooledDataSource(); 通常一个程序只会创建一个连接池
DBUtils:
QueryRunner(池子)
update(sql,params...)
query(sql,ResultSetHandler,params...)
BeanHandler<User>(User.class);
BeanListHandler<User>(User.class)