Jdbc笔记(一)

来源：互联网发布：台铃t3电动车价格数据编辑：程序博客网时间：2024/06/10 01:48

一、复杂查询

1、连接查询

连接查询的from子句的连接语法格式为：

from TABLE1 join_type(表示下面的关键字) TABLE2 [on (join_condition)](可有可无) [where (query_condition)](可有可无)

TABLE1:左表

TABLE2：右表

1.1交叉链接(cross join):(下面两句一样结果)

SELECT * FROM customer CROSS JOIN orders;

SELECT * FROM customer,orders;

1.2内连接(inner join):

显式内连接：使用inner join关键字和on（连接条件）

select o.order_number,o.price,c.name,c.city from orders o inner join customer c on o.customer_id=c.id;

也可以写为:

select o.order_number 订单号码,o.price 订单价格,c.name 客户,c.city 地址 from orders oinner join customer c on o.customer_id=c.id;

隐式内连接：不使用inner join关键字，使用where(只能使用表达式)设置链接条件

select o.order_number,o.price,c.name,c.city from orders o,customer c where o.customer_id=c.id;

也可以写为:

select o.order_number 订单号码,o.price 订单价格,c.name 客户,c.city 地址 from orders o,customer c where o.customer_id=c.id;

1.3外连接(outer join)

左外链接(left):返回满足链接条件的数据，同时还返回左表中不满足条件的剩余记录。

显示所有的订单(左表)，如果有对应的客户(右表)，把客户信息显示出来。(显示所有订单(左表),没有订单的客户(右表)不显示)

select * from orders o leftouter join customer c on o.customer_id=c.id;

右外链接(right):

显示所有的客户(右表)，如果有订单(左表)，把订单显示出来。(显示所有客户(右表),没有客户的订单(左表),不显示)

select * from orders o right outer join customer c on o.customer_id=c.id;

2、子查询:嵌套查询

查询陈冠希的订单详情

select id from customer where name='陈冠希';(需要先确认name为陈冠希的id)

select * from orders where customer_id=1;(然后通过这个id来查找他的订单)

//子查询,可以把以上两句命令嵌套在一起进行查询.

select * from orders where customer_id=(select id from customer where name='陈冠希');

3、联合查询(联合查询 union 关键字把两条语句联合到一起,取并集)

SELECT * FROM orders WHERE price>200 UNION SELECT * FROM orders WHERE customer_id=1;

3 7 1 2 3 UNION 1237

4、报表查询(很重要,开发中经常出现);

[select …] from … [where…] [ group by … [having… ]] [ order by … ]

其中group by 子句指定按照哪些字段分组，having子句设定分组查询条件。

注意: ---------------------

统计一个班级共有多少学生？

select count(*) from student;

select count(id) from student;//id是主键或者不为null，可以。

统计数学成绩大于90的学生有多少个？

select count(*) from student where math>=90;

统计总分大于250的人数有多少？

select count(*) from student where (chinese+english+math)>250;

统计一个班级数学总成绩？

select sum(math) from student;

统计一个班级语文、英语、数学各科的总成绩

select sum(chinese),sum(english),sum(math) from student;

统计一个班级语文、英语、数学的成绩总和

select sum(chinese+math+english) from student;

统计一个班级语文成绩平均分

select sum(chinese)/count(*) from student;

求一个班级数学平均分？

select avg(math) from student;

求一个班级总分平均分

select avg(chinese+math+english) from student;

-----------------------------------

Tips:如果使用数据库关键字作为普通名称，要使用``（与波浪线一个按键上的那个）引起来

对订单表中商品归类后，显示每一类商品的总价

select product,sum(price) from order1 groupby product;//按照product分组,搜索的项目是product,sum(price)

查询购买了几类商品，并且每类总价大于100的商品:having必须跟在group by后面，对分组后的数据进一步的筛选。having可以使用统计函数。

select product,sum(price) from order1 group by producthaving sum(price)>100;

count(1) count(2) 查询结果一样,where 1=1,返回所有.

如果使用数据库关键字作为普通名称使用,要用`order ` 引起来 esc按键下面的键

group 不能使用where,使用having,可以使用统计函数,不能使用表达式

二、数据库备份与数据库恢复

1、数据库的备份：

shell>mysqldump -u root -psorry day10>e:/day10.sql

2、数据库的恢复：

前提：书库名称必须存在：create database day10;

方式一：

mysql>use DAY10;

mysql>source e:/day10.sql;

方式二：(系统命令行下)

shell>mysql -u root -psorry day10<e:/day10.sql

注意命令中如果\的话,表示制表符,所以要用/.

shell就是window或者Linux 系统

三、JDBC简介

JDBC全称为：Java Data Base Connectivity（java数据库连接），它主要由接口组成。

JDBC属于JavaEE技术之一。它的API类在JDK中。是SUN公司制定的规范

数据库厂商就对JDBC进行了实现，所谓的驱动。

java.sql.*

javax.sql.*

开发环境：JDBC+数据库对应的驱动

四、JDBC编码步骤

1、注册驱动

不能直接new一个com.mysql.jdbc.Driver,

//DriverManager.registerDriver(new com.mysql.jdbc.Driver());

因为这样1、依赖具体的数据库驱动；2、会导致驱动被注册2遍.

Class.forName("com.mysql.jdbc.Driver");

2、获取与数据库的连接

//方式一：

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day10", "root", "sorry");

//方式二：

Properties props = new Properties();

props.put("user", "root");//key看数据库的规定

props.put("password", "sorry");

props.put("useUnicode", "true");

props.put("characterEncoding", "utf8");

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day10", props);

//方式三：

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day10?user=root&password=sorry");

注意,数据库的URL需要查看相关文档,不同数据库不同版本,URL都不一定一样.

URL: 协议(jdbc):子协议(mysql):主机:端口/数据库

3、得到代表SQL语句的对象

Statement stmt = conn.createStatement();

4、发送SQL语句：DML、DQL

ResultSet rs = stmt.executeQuery("selcet chinese,english,math from student");

//Statement常用方法

//ResultSet executeQuery(String sql):sql一般是DQL语句

//int executeUpdate(String sql):sql一般是没有返回结果集的语句，比如DML、DDL。返回值是影响到的行数

//boolean execute(String sql):sql可以任何的语句。返回值：如果执行的sql语句有结果集，返回true，没有结果集，返回false

5、如果是DQL语句，有结果，得到返回的结果

6、遍历结果集

while(rs.next()){

System.out.println("---------------------------");

System.out.print(rs.getObject("chinese")+"\t");

System.out.print(rs.getObject("english")+"\t");

System.out.println(rs.getObject("math"));

}

7、释放占用的资源(官方文档,最好写成工具类.)

//JDBC工具类

public class JdbcUtil {

private static String driverClass;

private static String url;

private static String user;

private static String password;

static{

try {

//从配置文件中读取信息

InputStream in = JdbcUtil.class.getClassLoader().getResourceAsStream("dbcfg.properties");

Properties pro = new Properties();

pro.load(in);

driverClass = pro.getProperty("driverClass");

url = pro.getProperty("url");

user = pro.getProperty("user");

password = pro.getProperty("password");

Class.forName(driverClass);

} catch (Exception e) {

throw new ExceptionInInitializerError(e);

}

public static Connection getConnection() throws Exception {

return DriverManager.getConnection(url,user,password);

}

public static void release(ResultSet rs,Statement stat,Connection conn){

if(rs!=null){

try {

rs.close();

} catch (SQLException e) {

e.printStackTrace();

}

rs = null;

}

if(stat!=null){

try {

stat.close();

} catch (SQLException e) {

e.printStackTrace();

}

stat = null;

}

if(conn!=null){

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

conn = null;

}

五、JDBC中常用接口详解

DriverManager：

注册驱动

获取数据库的链接

六、JDBC对数据库中的数据进行CRUD,增删改查

public void testInsert(){

Connection conn = null;

Statement stmt = null;

ResultSet rs = null;

try {

conn = JdbcUtil.getConnection();

stmt = conn.createStatement();

stmt.executeUpdate("insert into users (name,password,email,birthday) values('任敏','123','rm@itheima.com','1989-09-19')");

} catch (Exception e) {

e.printStackTrace();

}finally{

JdbcUtil.release(rs, stmt, conn);

}

public void testUpdate(){

Connection conn = null;

Statement stmt = null;

ResultSet rs = null;

try {

conn = JdbcUtil.getConnection();

stmt = conn.createStatement();

stmt.executeUpdate("update users set password='1234' where id=6");

} catch (Exception e) {

e.printStackTrace();

}finally{

JdbcUtil.release(rs, stmt, conn);

}

public void testDelete(){

Connection conn = null;

Statement stmt = null;

ResultSet rs = null;

try {

conn = JdbcUtil.getConnection();

stmt = conn.createStatement();

for(int x=8;x<10;x++)

stmt.executeUpdate("delete from users where id="+x);

} catch (Exception e) {

e.printStackTrace();

}finally{

JdbcUtil.release(rs, stmt, conn);

}

七、用MySQL来改写原来的用户注册和登陆案例

public void addUser(User user) {

Connection conn = null;

Statement stmt = null;

try {

conn = JdbcUtil.getConnection();

stmt = conn.createStatement();

stmt.executeUpdate("insert into user (username,nick,password,email,birthday) values('"+user.getUsername()+"','"+user.getNick()+"','"+user.getPassword()+"','"+user.getEmail()+"','"+user.getBirthday().toLocaleString()+"')");

} catch (Exception e) {

throw new DaoException(e);

}finally{

JdbcUtil.release(null, stmt, conn);

}

public User findUser(String username, String password) {

Connection conn = null;

Statement stmt = null;

ResultSet rs = null;

try {

conn = JdbcUtil.getConnection();

stmt = conn.createStatement();

String str = "select username,nick,password,email,birthday from user where username='"+username+"' and password='"+password+"'";

rs = stmt.executeQuery(str);

if(rs.next()){

User user = new User();

user.setUsername(rs.getString("username"));

user.setNick(rs.getString("nick"));

user.setPassword(rs.getString("password"));

user.setEmail(rs.getString("email"));

user.setBirthday(rs.getDate("birthday"));

return user;

}else{

return null;

}

} catch (Exception e) {

throw new DaoException(e);

}finally{

JdbcUtil.release(rs, stmt, conn);

}

八、PreparedStatement：日后都用它

SQL 注入是用户利用某些系统没有对输入数据进行充分的检查，从而进行恶意破坏的行为。

1、statement存在sql注入攻击问题，例如登陆用户名采用' or 1=1 or name='

2、对于防范 SQL 注入，可以采用PreparedStatement取代Statement。

它是Statement的子接口

作用：

支持预编译SQL：执行效率高。Statement是先编译,然后执行.而这个类创建之初就会编译SQL语句,虚拟机直接运行

支持参数占位符：?

防止SQL注入

public void addUser(User user) {

Connection conn = null;

PreparedStatement stmt = null;

try{

conn = JdbcUtil.getConnection();

stmt = conn.prepareStatement("insert into user (username,nick,password,email,birthday) values (?,?,?,?,?)");

stmt.setString(1, user.getUsername());

stmt.setString(2, user.getNick());

stmt.setString(3, user.getPassword());

stmt.setString(4, user.getEmail());

//这里的Date不能向下转型,子类的实例对象可以转成父类,但是父类的实例对象不能转成子类

stmt.setDate(5, new java.sql.Date(user.getBirthday().getTime()));

//记住,这里还需要插入动作.

stmt.executeUpdate();

}catch(Exception e){

throw new DaoException(e);

}finally{

JdbcUtil.release(null, stmt, conn);

}

九、DAO解耦

把控制权转移到外面,想要创建对象,必须先传参数依赖注入

public void setDao(User dao){

this.dao = dao;

{

另一种方式通过构造函数转过来

单开就是单例设计模式

//到底使用哪一个，是由自己指定的。是new出来的,没法解耦

//如果由外部传入使用的实现类，这个过程称之为控制反转,就可以解耦了.IoC DI:依赖注入:Spring的核心

//工厂创建模式,就是把创建的细节隐藏起来.

private UserDao dao = DaoFactory.getInstance().getUserDaoImpl();// = new UserDaoMySQLEnhanceImpl();

// public UserServiceImpl(UserDao dao){//依赖注入方法一,通过构造方式

// this.dao = dao;

// }

// public void setDao(UserDao dao){//依赖注入方式二,通过set方法

// this.dao = dao;

// }

//创建DAO实例的工厂:饿汉子式单例

public class DaoFactory {

//先创建工厂类,需要私有化,为了能一家在就实例化,需要静态修饰

private static DaoFactory instance = new DaoFactory();

//无参构造函数私有化

private DaoFactory(){}

//定义静态方法,调用类

public static DaoFactory getInstance(){

return instance;

}

//私有Properties类,

private static Properties props = new Properties();

static{

//静态代码块,初始化就加载,这样就参数就可以使用字符串,就可以写到配置文件中.

InputStream in = DaoFactory.class.getClassLoader().getResourceAsStream("dao.properties");

try {

props.load(in);

} catch (IOException e) {

throw new RuntimeException(e);

}

//创建一个UserDaoImpl实例类,需要的参数读取配置文件

//这样就进行了解耦,所有需要的参数可以通过修改配置文件来完成

public UserDao getUserDaoImpl(){

try {

String daoImplName = props.getProperty("userDao");

return (UserDao)Class.forName(daoImplName).newInstance();

} catch (Exception e) {

throw new RuntimeException(e);

}

十、大数据（大文本和大二进制）存取

0 0