学习笔记-mysql基本操作

数据库

数据库基本概念：

　　DDL：数据库的定义，与数据库/表结构：create,drop,alter

　　DML：数据库操纵语言：操作表数据 insert update delete

　　DCl：数据库控制语言：设置用户的访问权限 安全

　　DQL：数据库查询语言：Select from where

数据库的基本操作：

　　创建数据库：create databases 数据库的名字

　　查看数据库：1、查看所有数据库：show databases;

　　　　　　　　2、查看数据库定义：show create database 数据库名

　　　　　　　　3、查看当前正在使用的数据库：select database();

　　修改数据库：later database character set 字符集

　　删除数据库：drop database 数据库名字

　　选中数据库：use 数据库的名字

 表结构的基本操作：

　　创建表：1、create table 表名（

　　　　　　　　　　列名 列的类型（长度） 约束，

　　　　　　　　　　列名 列的类型（长度） 约束

　　　　　　　　）；

　　　　　　2列的约束：①主键约束：primary key

　　　　　　　　　　　  ②唯一约束：unique

　　　　　　　　　　　  ③非空约束：not null

　　删除表：drop table 表名

　　查看表：1、show tables：查看当前数据库中的所有的表名

　　　　　　2、show create table 表名:查看表的定义结构/创建语句

　　　　　　3、desc 表名：查看表的结构

　　修改表：1、添加列：alter table 表名 add 列名 列的类型 列的约束

　　　　　　2、修改列：alter table 表名 modify 列名 列的类型 列的约束

　　　　　　3、修改列名：alter table 表名 change 旧列名 新列名 列的类型 列的约束

　　　　　　4、删除列：alter table 表名 drop 列名

　　　　　　 5、修改表的字符集：alter table 表名 character set 字符集

　　　　　　6、修改表名:rename table 旧表名 to 新的表名

表中数据的CRUD操作：

　　插入数据：1、insert into 表名（列名1，列名2）values(值1，值2）；

　　　　　　　2、insert into 表名values (值1，值2）；

　　　　　　　3、insert into 表名values（值1，值2），（值1，值2），（值1，值2）；——批量插入

　　删除数据：1、delete from 表名【where 条件】

　　　　　　　2、truncate table 表名；——先删除表，再创建表

　　更新数据：update 表名 set 列名=值，列名=值【where 条件】

　　查询数据：1、通用格式：select 【distinct】【*】【列名1，列名2】 from 表名 where 条件 group by....having 条件过滤 order by 排序

 　　　　　　　2、关系运算符：and or not

　　　　　　　3、在范围内：in

　　　　　　　4、模糊查询：like （_表示的单个字符，%表示 的是多个字符）

　　　　　　　5、别名查询：as

　　　　　　　6、聚合函数：求和：sum()

　　　　　　　　　　　　　  平均值：avg()

　　　　　　　　　　　　　  统计数量：count()

　　　　　　　　　　　　　  最大值：max()

　　　　　　　　　　　　　 最小值：min()

 　　　　　　　7、排序：order by   ：asc：ascend

　　　　　　　　　　　　　　　　　desc：descend

　　　　　　　8、分组：group by

 多表之间的关系如何来维护

     外键约束: foreign key

　　alter table 前者 add foreign key(前者id)  references  后者(后者id);

     首先得去前表, 删除所有分类后者id的表项

建数据库原则:

　　通常情况下,一个项目/应用建一个数据库

多表之间的建表原则

　　 一对多 : 商品和分类

　　　　 建表原则: 在多的一方添加一个外键,指向一的一方的主键

　　多对多: 老师和学生, 学生和课程

　　　　建表原则: 建立一张中间表,将多对多的关系,拆分成一对多的关系,中间表至少要有两个外键,分别指向原来的那两张表

　　一对一: 班级和班长, 公民和身份证, 国家和国旗

　　　　建表原则:  将一对一的情况,当作是一对多情况处理,在任意一张表添加一个外键,并且这个外键要唯一,指向另外一张表

      　　　　1、直接将两张表合并成一张表

      　　　　2、将两张表的主键建立起连接,让两张表里面主键相等

 　　　　　　　　实际用途: 用的不是很多.    (拆表操作  )

　　　　　　　　　　　　拆表操作 : 将个人的常用信息和不常用信息,减少表的臃肿。

用户表 (用户的ID,用户名,密码,手机)

  ```sql
  create table user(
      uid int primary key auto_increment,
        username varchar(31),
        password varchar(31),
        phone  varchar(11)
  );

  insert into user values(1,'zhangsan','123','13811118888');
  ```
订单表 (订单编号,总价,订单时间 ,地址,外键用户的ID)

  ```sql
    create table orders(
        oid int primary key auto_increment,
          sum int not null,
        otime timestamp,
          address varchar(100),
          uno int,
          foreign key(uno) references user(uid)
    );
    insert into orders values(1,200,null,'黑马前台旁边小黑屋',1);
    insert into orders values(2,250,null,'黑马后台旁边1702',1);
  ```

商品表 (商品ID, 商品名称,商品价格,外键cno)

    ```sql
    create table product(
        pid int primary key auto_increment,
          pname varchar(10),
          price double,
          cno int,
          foreign key(cno) references category(cid)
    );

    insert into product values(null,'小米mix4',998,1);
    insert into product values(null,'锤子',2888,1);
    insert into product values(null,'阿迪王',99,2);
    insert into product values(null,'老村长',88,3);
    insert into product values(null,'劲酒',35,3);
    insert into product values(null,'小熊饼干',1,4);
    insert into product values(null,'卫龙辣条',1,5);
    insert into product values(null,'旺旺大饼',1,5);
    ```

 订单项: 中间表(订单ID,商品ID,商品数量,订单项总价)

  ```sql
  create table orderitem(
      ono int,
        pno int,
        foreign key(ono) references orders(oid),
        foreign key(pno) references product(pid),
        ocount int,
        subsum double
  );
  --给1号订单添加商品 200块钱的商品
  insert into orderitem values(1,7,100,100);
  insert into orderitem values(1,8,101,100);

  --给2号订单添加商品 250块钱的商品 ()
  insert into orderitem values(2,5,1,35);
  insert into orderitem values(2,3,3,99);
  ```

商品分类表(分类ID,分类名称,分类描述)

    ```sql
    create table category(
        cid int primary key auto_increment,
          cname varchar(15),
          cdesc varchar(100)
    );

    insert into category values(null,'手机数码','电子产品,黑马生产');
    insert into category values(null,'鞋靴箱包','江南皮鞋厂倾情打造');
    insert into category values(null,'香烟酒水','黄鹤楼,茅台,二锅头');
    insert into category values(null,'酸奶饼干','娃哈哈,蒙牛酸酸乳');
    insert into category values(null,'馋嘴零食','瓜子花生,八宝粥,辣条');
    ```

交叉连接查询  笛卡尔积

　　笛卡尔积 ,查出来是两张表的乘积 ,查出来的结果没有意义

　　SELECT * FROM表一名，表二名;

内连接查询

　　隐式内链接

            SELECT * FROM表一名 p,表二名 c WHERE p.cno=c.cid;

　　显示内链接

            SELECT * FROM 表一名 p INNER JOIN 表二名 c ON p.cno=c.cid;    

　　区别:

              隐式内链接: 在查询出结果的基础上去做的WHERE条件过滤

              显示内链接: 带着条件去查询结果, 执行效率要高

左外连接

            左外连接,会将左表中的所有数据都查询出来, 如果右表中没有对应的数据,用NULL代替

              SELECT * FROM 表一名 p LEFT OUTER JOIN 表二名 c ON p.cno=c.cid;

右外连接: 会将右表所有数据都查询出来, 如果左表没有对应数据的话, 用NULL代替

            SELECT * FROM 表一名 p RIGHT OUTER JOIN 表二名 c ON p.cno=c.cid;

子查询：

  　　 SELECT * FROM 表一名WHERE cno = (SELECT cid FROM 表二名 WHERE cname='手机数码');

 分页查询：

　　select * from 表名 limit 0,3;

　　第一个参数是索引

　　第二个参数显示的个数

CREATE TABLE emp(
    empno INT,
    ename VARCHAR(50),
    job VARCHAR(50),
    mgr    INT,
    hiredate DATE,
    sal    DECIMAL(7,2),
    comm DECIMAL(7,2),
    deptno INT
) ;

INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
INSERT INTO emp VALUES(7981,'MILLER','CLERK',7788,'1992-01-23',2600,500,20);


CREATE TABLE dept(
    deptno        INT,
    dname        VARCHAR(14),
    loc        VARCHAR(13)
);

INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES(30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES(40, 'OPERATIONS', 'BOSTON');


DESC SELECT dname FROM dept WHERE deptno=10;


-- 单行子查询(> < >= <= = <>)
    -- 查询出高于10号部门的平均工资的员工信息
    1.查询出10号部门的平均工资
    SELECT AVG(sal) FROM emp WHERE deptno = 10;
    2.查询出高于10号部门平均工资的员工信息
    SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno = 10);
-- 多行子查询(in  not in any all)    >any  >all
    -- 查询出比10号部门任何员工薪资高的员工信息
    1. 查询出10号部门的所有工资信息
    SELECT sal FROM emp WHERE deptno=10;
    2.得出结果
    SELECT * FROM emp WHERE sal >ALL(SELECT sal FROM emp WHERE deptno=10); 

-- 多列子查询(实际使用较少)   in
    -- 和10号部门同名同工作的员工信息
    1.查出10号部门员工名字和工作信息
    SELECT ename,job FROM emp WHERE deptno=10;
    2. 得出结果:
    SELECT * FROM emp WHERE (ename,job) IN (SELECT ename,job FROM emp WHERE deptno=10) AND deptno!=10;
-- Select接子查询
    -- 获取员工的名字和部门的名字
    1.查出员工的名字和部门编号信息
    SELECT ename,deptno FROM emp;
    2.得出结果
    SELECT ename,(SELECT dname FROM dept WHERE dept.deptno=emp.deptno) FROM emp;
-- from后面接子查询
    -- 查询emp表中经理信息
    1.查询出所有经理的ID
    SELECT DISTINCT mgr FROM emp;
    2. 查出经理的信息,只要id在第一步的查询结果中就可以了
    SELECT * FROM emp,(SELECT DISTINCT mgr FROM emp) mgrs WHERE emp.empno = mgrs.mgr;
-- where 接子查询
    -- 薪资高于10号部门平均工资的所有员工信息
    1.查询出10号部门的平均工资
    SELECT AVG(sal) FROM emp WHERE deptno=10;
    2.得出结果:
    SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno=10);
-- having后面接子查询
    -- 有哪些部门的平均工资高于30号部门的平均工资
    1.30号部门的平均工资
    SELECT AVG(sal) FROM emp WHERE deptno=30;
    2. 统计所有部门的平均工资
    SELECT deptno,AVG(sal) FROM emp GROUP BY deptno ;
    3. 得出最终的结果
    SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) > (SELECT AVG(sal) FROM emp WHERE deptno=30);
        
-- 工资>JONES工资的员工信息
    1.查处JONES的工资信息
    SELECT sal FROM emp WHERE ename='JONES';
    2.得出结果
    SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='JONES');
-- 查询与SCOTT同一个部门的员工
    1.查处SCOTT的部门编号
    SELECT deptno FROM emp WHERE ename = 'SCOTT';
    2.得出结果
    SELECT * FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'SCOTT');
-- 工资高于30号部门所有人的员工信息
    1.得出30号部门的最大工资
    SELECT MAX(sal) FROM emp WHERE deptno = 30;
    2.得出结果
    SELECT * FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30);
-- 查询工作和工资与MARTIN完全相同的员工信息
    1.查处MARTIN的工作和工资
    SELECT job,sal FROM emp WHERE ename='MARTIN';
    2.得出结果
    SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename='MARTIN');
-- 有两个以上直接下属的员工信息
    1.查出emp表中mgr信息
    SELECT mgr FROM emp;
    2.分组统计mgr的信息
    SELECT mgr,COUNT(*) FROM emp GROUP BY mgr HAVING COUNT(*)>2;
    3.得出结果
    SELECT * FROM emp e1 WHERE e1.empno IN (SELECT e2.mgr FROM emp e2 GROUP BY e2.mgr HAVING COUNT(*)>2);
-- 查询员工编号为7788的员工名称,员工工资,部门名称,部门地址
    1.将员工表和部门表链接起来
    SELECT * FROM emp ,dept WHERE emp.deptno = dept.deptno;
    2.得出ID为7788的所有信息
    SELECT * FROM emp ,dept WHERE emp.deptno = dept.deptno AND empno=7788;
    3.只显示其中的需要的信息
    SELECT ename,sal ,dname, loc FROM emp ,dept WHERE emp.deptno = dept.deptno AND empno=7788;

-- 1. 查询出高于本部门平均工资的员工信息
    1. 分组统计每个部门的平均工资
    SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;
    2. 得出相应的结果
    SELECT * FROM emp e1 WHERE e1.sal > (SELECT AVG(e2.sal) FROM emp e2 WHERE e1.deptno=e2.deptno  GROUP BY e2.deptno);
    
-- 1. 列出达拉斯加工作的人中,比纽约平均工资高的人
    0. 查处DALLAS 的部门编号
    SELECT deptno FROM dept WHERE loc='DALLAS';
    1.查处达拉斯工作的人的信息
    SELECT * FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE loc='DALLAS');
    2. 查处纽约的部门编号
    SELECT deptno FROM dept WHERE loc='NEW YORK';
    3. 查村纽约平均工资
    SELECT AVG(sal) FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE loc='NEW YORK');
    4.得出结果:
    SELECT * FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE loc='DALLAS')
    AND sal > (SELECT AVG(sal) FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE loc='NEW YORK'));
    
    
-- 2. 查询7369员工编号,姓名,经理编号和经理姓名
    SELECT e1.empno,e1.ename,e1.mgr,mgrtable.ename FROM emp e1,emp mgrtable WHERE e1.mgr = mgrtable.empno; 
    
-- 3. 查询出各个部门薪水最高的员工所有信息
    1.分组统计每个部门员工最高的薪资是多少
    SELECT MAX(sal),deptno FROM emp GROUP BY deptno;
    2.算出结果
    SELECT * FROM emp e1 WHERE e1.sal = (SELECT MAX(sal) FROM emp e2 WHERE e1.deptno = e2.deptno GROUP BY deptno);
    
    
CREATE TABLE test(
  NAME CHAR(20),
  kecheng CHAR(20),
  fenshu CHAR(20)
);

INSERT INTO test VALUES
('张三','语文',81),
('张三','数学',75),
('李四','语文',76),
('李四','数学',90),
('王五','语文',81),
('王五','数学',82);


SELECT NAME FROM test WHERE fenshu < 80 ;
SELECT NAME FROM test WHERE NAME NOT IN(SELECT NAME FROM test WHERE fenshu < 80 );

    
    

 JDBC

JDBC概念和数据库驱动程序

JDBC（Java Data Base Connectivity,java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。是Java访问数据库的标准规范

JDBC提供了一种基准,据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序。

JDBC需要连接驱动，驱动是两个设备要进行通信，满足一定通信数据格式，数据格式由设备提供商规定，设备提供商为设备提供驱动软件，通过软件可以与该设备进行通信。

总结：  JDBC是java提供给开发人员的一套操作数据库的接口

                     数据库驱动就是实现该接口的实现类

JDBC原理

              Java提供访问数据库规范称为JDBC，而生产厂商提供规范的实现类称为驱动

              JDBC是接口，驱动是接口的实现，没有驱动将无法完成数据库连接，从而不能操作数据库！每个数据库厂商都需要提供自己的驱动，用来连接自己公司的数据库，也就是说驱动一般都由数据库生成厂商提供。

JDBC的开发步骤

       JDBC的开发步骤

                     1.注册驱动

                            告知JVM使用的是哪一个数据库的驱动

                     2.获得连接

                            使用JDBC中的类,完成对MySQL数据库的连接

                     3.获得语句执行平台

                            通过连接对象获取对SQL语句的执行者对象

                     4.执行sql语句

                            使用执行者对象,向数据库执行SQL语句

                            获取到数据库的执行后的结果

                     5.处理结果

                     6.释放资源  一堆close()             

public class JDBCDemo {
                public static void main(String[] args)throws ClassNotFoundException,SQLException{
                    //1.注册驱动 反射技术,将驱动类加入到内容
                    // 使用java.sql.DriverManager类静态方法 registerDriver(Driver driver)
                    // Diver是一个接口,参数传递,MySQL驱动程序中的实现类
                    //DriverManager.registerDriver(new Driver());
                    //驱动类源代码,注册2次驱动程序
                    Class.forName("com.mysql.jdbc.Driver");
                    
                    //2.获得数据库连接  DriverManager类中静态方法
                    //static Connection getConnection(String url, String user, String password)  
                    //返回值是Connection接口的实现类,在mysql驱动程序
                    //url: 数据库地址  jdbc:mysql://连接主机IP:端口号//数据库名字
                    String url = "jdbc:mysql://localhost:3306/mybase";
                    String username="root";
                    String password="123";
                    Connection con = DriverManager.getConnection(url, username, password);
                    
                    //3.获得语句执行平台, 通过数据库连接对象,获取到SQL语句的执行者对象
                    // con对象调用方法   Statement createStatement() 获取Statement对象,将SQL语句发送到数据库
                    // 返回值是 Statement接口的实现类对象,,在mysql驱动程序
                    Statement stat = con.createStatement();
                    //    4.执行sql语句
                    // 通过执行者对象调用方法执行SQL语句,获取结果
                    // int executeUpdate(String sql)  执行数据库中的SQL语句, insert delete update
                    // 返回值int,操作成功数据表多少行
                    int row = stat.executeUpdate
                            ("INSERT INTO sort(sname,sprice,sdesc) VALUES('汽车用品',50000,'疯狂涨价')");
                    System.out.println(row);
                    
                    //6.释放资源  一堆close()
                    stat.close();
                    con.close();
                }
            }

 

导入mysql数据库驱动程序jar包

              步骤

                     创建lib目录，用于存放当前项目需要的所有jar包

                     选择jar包，右键执行build path / Add to Build Path

 

执行select语句获取结果集：

public class JDBCDemo1 {
                public static void main(String[] args) throws Exception{
                    //1. 注册驱动
                    Class.forName("com.mysql.jdbc.Driver");
                    //2. 获取连接对象
                    String url = "jdbc:mysql://localhost:3306/mybase";
                    String username="root";
                    String password="123";
                    Connection con = DriverManager.getConnection(url, username, password);
                    //3 .获取执行SQL 语句对象
                    Statement stat = con.createStatement();
                    // 拼写查询的SQL
                    String sql = "SELECT * FROM sort";
                    //4. 调用执行者对象方法,执行SQL语句获取结果集
                    // ResultSet executeQuery(String sql)  执行SQL语句中的select查询
                    // 返回值ResultSet接口的实现类对象,实现类在mysql驱动中
                    ResultSet rs = stat.executeQuery(sql);
                    //5 .处理结果集
                    // ResultSet接口方法 boolean next() 返回true,有结果集,返回false没有结果集
                    while(rs.next()){
                        //获取每列数据,使用是ResultSet接口的方法 getXX方法参数中,建议写String列名
                        System.out.println(rs.getInt("sid")+"   "+rs.getString("sname")+
                                "   "+rs.getDouble("sprice")+"   "+rs.getString("sdesc"));
                    }
                    
                    rs.close();
                    stat.close();
                    con.close();
                }
            }

SQL注入攻击

              注入问题

                      假设有登录案例SQL语句如下:

                      SELECT * FROM 用户表 WHERE NAME = 用户输入的用户名 AND PASSWORD = 用户输的密码;

                      此时，当用户输入正确的账号与密码后，查询到了信息则让用户登录。但是当用户输入的账号为XXX 密码为：XXX’  OR ‘a’=’a时，则真正执行的代码变为： * SELECT * FROM 用户表 WHERE NAME = ‘XXX’ AND PASSWORD =’ XXX’  OR ’a’=’a’;

                     此时，上述查询语句时永远可以查询出结果的。那么用户就直接登录成功了，显然我们不希望看到这样的结果，这便是SQL注入问题。

            public class JDBCDemo2 {
                public static void main(String[] args)throws Exception {
                    Class.forName("com.mysql.jdbc.Driver");
                    String url = "jdbc:mysql://localhost:3306/mybase";
                    String username = "root";
                    String password = "123";
                    Connection con = DriverManager.getConnection(url, username, password);
                    Statement stat = con.createStatement();
                    
                    Scanner sc = new Scanner(System.in);
                    String user = sc.nextLine();
                    String pass = sc.nextLine();
                    
                    //执行SQL语句,数据表,查询用户名和密码,如果存在,登录成功,不存在登录失败
            //        String sql = "SELECT * FROM users WHERE username='dsfsdfd' AND PASSWORD='wrethiyu' OR 1=1";
                    String sql = "SELECT * FROM users WHERE username='"+user+"' AND PASSWORD='"+pass+"'";
                    System.out.println(sql);
                    ResultSet rs = stat.executeQuery(sql);
                    while(rs.next()){
                        System.out.println(rs.getString("username")+"   "+rs.getString("password"));
                    }
                    
                    rs.close();
                    stat.close();
                    con.close();
                }
            }

PrepareStatement接口预编译SQL语句：

              预处理对象：

                     使用PreparedStatement预处理对象时，建议每条sql语句所有的实际参数，都使用逗号分隔。

                     String sql = "insert into sort(sid,sname) values(?,?)";

                      PreparedStatement预处理对象代码：

                     　　PreparedStatement psmt = conn.prepareStatement(sql)

              执行SQL语句的方法介绍：

                     int executeUpdate(); --执行insert update delete语句.

                     ResultSet executeQuery(); --执行select语句.

                     boolean execute(); --执行select返回true 执行其他的语句返回false.

              设置实际参数：

                     void setXxx(int index, Xxx xx) 将指定参数设置为给定Java的xx值。在将此值发送到数据库时，驱动程序将它转换成一个 SQL Xxx类型值。

                      例如： setString(2, "家用电器") 把SQL语句中第2个位置的占位符？替换成实际参数 "家用电器" 

/*
             *  Java程序实现用户登录,用户名和密码,数据库检查
             *  防止注入攻击
             *  Statement接口实现类,作用执行SQL语句,返回结果集
             *  有一个子接口PreparedStatement  (SQL预编译存储,多次高效的执行SQL) 
             *  PreparedStatement的实现类数据库的驱动中,如何获取接口的实现类
             *  
             *  是Connection数据库连接对象的方法
             *  PreparedStatement prepareStatement(String sql) 
                      
             */
            public class JDBCDemo3 {
                public static void main(String[] args)throws Exception {
                    Class.forName("com.mysql.jdbc.Driver");
                    String url = "jdbc:mysql://localhost:3306/mybase";
                    String username = "root";
                    String password = "123";
                    Connection con = DriverManager.getConnection(url, username, password);
                    Scanner sc = new Scanner(System.in);
                    String user = sc.nextLine();
                    String pass = sc.nextLine();
                    
                    //执行SQL语句,数据表,查询用户名和密码,如果存在,登录成功,不存在登录失败
                    String sql = "SELECT * FROM users WHERE username=? AND PASSWORD=?";
                    //调用Connection接口的方法prepareStatement,获取PrepareStatement接口的实现类
                    //方法中参数,SQL语句中的参数全部采用问号占位符
                    PreparedStatement pst =  con.prepareStatement(sql);
                    System.out.println(pst);
                    //调用pst对象set方法,设置问号占位符上的参数
                    pst.setObject(1, user);
                    pst.setObject(2, pass);
                    
                    //调用方法,执行SQL,获取结果集
                    ResultSet rs = pst.executeQuery();
                    while(rs.next()){
                        System.out.println(rs.getString("username")+"   "+rs.getString("password"));
                    }
                    
                    rs.close();
                    pst.close();
                    con.close();
                }
            }

PrepareStatement接口预编译SQL语句执行修改

            /*
             *  使用PrepareStatement接口,实现数据表的更新操作
             */
            public class JDBCDemo {
                public static void main(String[] args) throws Exception{
                    Class.forName("com.mysql.jdbc.Driver");
                    String url = "jdbc:mysql://localhost:3306/mybase";
                    String username="root";
                    String password="123";
                    Connection con = DriverManager.getConnection(url, username, password);    
                    
                    //拼写修改的SQL语句,参数采用?占位
                    String sql = "UPDATE sort SET sname=?,sprice=? WHERE sid=?";
                    //调用数据库连接对象con的方法prepareStatement获取SQL语句的预编译对象
                    PreparedStatement pst = con.prepareStatement(sql);
                    //调用pst的方法setXXX设置?占位
                    pst.setObject(1, "汽车美容");
                    pst.setObject(2, 49988);
                    pst.setObject(3, 7);
                    //调用pst方法执行SQL语句
                    pst.executeUpdate();
                    
                    pst.close();
                    con.close();
                }
            }

PrepareStatement接口预编译SQL语句执行查询

            /*
             *  PrepareStatement接口实现数据表的查询操作
             */
            public class JDBCDemo1 {
                public static void main(String[] args) throws Exception{
                    Class.forName("com.mysql.jdbc.Driver");
                    String url = "jdbc:mysql://localhost:3306/mybase";
                    String username="root";
                    String password="123";
                    Connection con = DriverManager.getConnection(url, username, password);    
                    
                    String sql = "SELECT * FROM sort";
                    
                    PreparedStatement pst = con.prepareStatement(sql);
                    
                    //调用pst对象的方法,执行查询语句,Select
                    ResultSet rs=pst.executeQuery();
                    while(rs.next()){
                        System.out.println(rs.getString("sid")+"  "+rs.getString("sname")+"  "+rs.getString("sprice")+"  "+rs.getString("sdesc"));
                    }
                    rs.close();
                    pst.close();
                    con.close();
                }
            }

JDBC的工具类和测试：

//JDBCUtils工具类代码
            public class JDBCUtils {
                private JDBCUtils(){}
                private static Connection con ;
                
                static{
                    try{
                        Class.forName("com.mysql.jdbc.Driver");
                        String url = "jdbc:mysql://localhost:3306/mybase";
                        String username="root";
                        String password="123";
                        con = DriverManager.getConnection(url, username, password);
                    }catch(Exception ex){
                        throw new RuntimeException(ex+"数据库连接失败");
                    }
                }
                
                /*
                 * 定义静态方法,返回数据库的连接对象
                 */
                public static Connection getConnection(){
                    return con;
                }
                
                
                public static void close(Connection con,Statement stat){
                     
                     if(stat!=null){
                         try{
                             stat.close();
                         }catch(SQLException ex){}
                     }
                     
                     if(con!=null){
                         try{
                             con.close();
                         }catch(SQLException ex){}
                     }
                     
                }
                
                
                public static void close(Connection con,Statement stat , ResultSet rs){
                     if(rs!=null){
                         try{
                             rs.close();
                         }catch(SQLException ex){}
                     }
                     
                     if(stat!=null){
                         try{
                             stat.close();
                         }catch(SQLException ex){}
                     }
                     
                     if(con!=null){
                         try{
                             con.close();
                         }catch(SQLException ex){}
                     }
                     
                }
            }

测试JDBCUtils工具类的代码
        public class TestJDBCUtils {
            public static void main(String[] args)throws Exception {
                Connection con = JDBCUtils.getConnection();
                PreparedStatement pst = con.prepareStatement("SELECT sname FROM sort");
                ResultSet rs = pst.executeQuery();
                while(rs.next()){
                    System.out.println(rs.getString("sname"));
                }
                JDBCUtils.close(con, pst, rs);
            }
        }

 properties配置文件

开发中获得连接的4个参数（驱动、URL、用户名、密码）通常都存在配置文件中，方便后期维护，程序如果需要更换数据库，只需要修改配置文件即可。

通常情况下，我们习惯使用properties文件，此文件做如下要求：

1. 文件位置：任意，建议src下
2. 文件名称：任意，扩展名为properties
3. 文件内容：一行一组数据，格式是“key=value”.

　　　　a)key命名自定义，如果是多个单词，习惯使用点分隔。例如：jdbc.driver

　　　　b)value值不支持中文，如果需要使用非英文字符，将进行unicode转换。

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mydb
user=root
password=root

public class JDBCUtils {

    private static String driver;
    private static String url;
    private static String user;
    private static String password;
    // 静态代码块
    static {
        try {
            // 1 使用Properties处理流
            // 使用load()方法加载指定的流
            Properties props = new Properties();
            Reader is = new FileReader("db.properties");
            props.load(is);
            // 2 使用getProperty(key)，通过key获得需要的值，
            driver = props.getProperty("driver");
            url = props.getProperty("url");
            user = props.getProperty("user");
            password = props.getProperty("password");
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 获得连接
     */
    public static Connection getConnection() {
        try {
            // 1 注册驱动
            Class.forName(driver);
            // 2 获得连接
            Connection conn = DriverManager.getConnection(url, user, password);
            return conn;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}

public class Demo {
    @Test
    public void insert(){
        try{
            //1,获取连接对象
            Connection conn = JDBCUtils.getConnection();
            //2,指定要执行的SQL语句
            String sql = "INSERT INTO zhangwu(name,money,parent) VALUES(?,?,?)";
            //4，获取SQL语句的执行对象 PreparedStatement
            PreparedStatement ppstat = conn.prepareStatement(sql);
            //5,执行SQL语句
            ppstat.setString(1, "股票收入");
            ppstat.setDouble(2, 5000);
            ppstat.setString(3, "收入");
            int line = ppstat.executeUpdate();
            //6,处理结果集
            System.out.println("line=" + line);
            //7,关闭连接
            ppstat.close();
            conn.close();
        } catch(SQLException e){
            throw new RuntimeException(e);
        }
    }
}

DBUtils

　如果只使用JDBC进行开发，我们会发现冗余代码过多，为了简化JDBC开发，本案例我们讲采用apache commons组件一个成员：DBUtils。

      DBUtils就是JDBC的简化开发工具包。需要项目导入commons-dbutils-1.6.jar才能够正常使用DBUtils工具。

　DBUtils是java编程中的数据库操作实用工具，小巧简单实用。

　DBUtils封装了对JDBC的操作，简化了JDBC操作，可以少写代码。

　Dbutils三个核心功能介绍：

　　QueryRunner中提供对sql语句操作的API.

　　ResultSetHandler接口，用于定义select操作后，怎样封装结果集.

　　DbUtils类，它就是一个工具类,定义了关闭资源与事务处理的方法

 QueryRunner核心类（np）

update(Connection conn, String sql, Object... params) ：用来完成表数据的增加、删除、更新操作

query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) ：用来完成表数据的查询操作

QueryRunner实现添加、更新、删除操作（update）

public void insert(){
    try {
        //获取一个用来执行SQL语句的对象   QueryRunner
        QueryRunner qr = new QueryRunner();
        
        String sql = "INSERT INTO zhangwu(name,money,parent) VALUES(?,?,?)";
        Object[] params = {"股票收入", 5500, "收入"};
        Connection conn = JDBCUtils.getConnection();
        int line = qr.update(conn,sql,params);// 用来完成表数据的增加、删除、更新操作
        //结果集处理
        System.out.println("line = " + line);
        
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

public void update(){
    try {
        //创建一个QueryRunner对象，用来完成SQL语句的执行
        QueryRunner qr = new QueryRunner();
        //执行SQL语句
        String sql = "UPDATE zhangwu SET money = money+1000 WHERE name=?";
        Object[] params = {"股票收入"};
        Connection conn = JDBCUtils.getConnection();
        int line = qr.update(conn, sql, params);
        //结果集的处理
        System.out.println("line="+line);
        
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

public void delete(){
    try {
        //创建一个QueryRunner对象，用来完成SQL语句的执行
        QueryRunner qr = new QueryRunner();
        //执行SQL语句
        String sql = "DELETE FROM zhangwu WHERE name = ?";
        Object[] params = {"股票收入"};
        Connection conn = JDBCUtils.getConnection();
        int line = qr.update(conn, sql, params);
        //结果集的处理
        System.out.println("line="+line);
        
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

QueryRunner实现查询操作（query）

结果集处理类:

ArrayHandler	将结果集中的第一条记录封装到一个Object[]数组中，数组中的每一个元素就是这条记录中的每一个字段的值
ArrayListHandler	将结果集中的每一条记录都封装到一个Object[]数组中，将这些数组在封装到List集合中。
BeanHandler	将结果集中第一条记录封装到一个指定的javaBean中。
BeanListHandler	将结果集中每一条记录封装到指定的javaBean中，将这些javaBean在封装到List集合中
ColumnListHandler	将结果集中指定的列的字段值，封装到一个List集合中
ScalarHandler	它是用于单数据。例如select count(*) from 表操作。
MapHandler	将结果集第一行封装到Map集合中,Key 列名, Value 该列数据
MapListHandler	将结果集第一行封装到Map集合中,Key 列名, Value 该列数据,Map集合存储到List集合

ArrayHandler与ArrayListHandler查询:

1、ArrayHandler：将结果集中的第一条记录封装到一个Object[]数组中，数组中的每一个元素就是这条记录中的每一个字段的值。

public class ArrayHandlerDemo {
    
    @Test
    public void method(){
        try {
            //获取QueryRunner对象
            QueryRunner qr = new QueryRunner();
            //执行SQL语句
            String sql = "SELECT * FROM zhangwu";
            Object[] params = {};
            Connection conn = JDBCUtils.getConnection();
            Object[] objArray = qr.query(conn, sql, new ArrayHandler(), params);
            //结果集的处理
            System.out.println( Arrays.toString(objArray) );
            
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

2、ArrayListHandler：将结果集中的每一条记录都封装到一个Object[]数组中，将这些数组在封装到List集合中。

public class ArrayListHandlerDemo {
    @Test
    public void method(){
        try {
            //获取QueryRunner对象
            QueryRunner qr = new QueryRunner();
            //执行SQL语句
            String sql = "SELECT * FROM zhangwu WHERE money>?";
            Object[] params = {2000};
            Connection conn = JDBCUtils.getConnection();
             List<Object[]> list = qr.query(conn, sql, new ArrayListHandler(), params);
            //结果集的处理
             for (Object[] objArray : list) {
                System.out.println(  Arrays.toString(objArray) );
            }
            
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

BeanHandler与BeanListHandler查询：

1、BeanHandler ：将结果集中第一条记录封装到一个指定的javaBean中。

public class BeanHandlerDemo {
    @Test
    public void method(){
        try{
            //获取QueryRunner 
            QueryRunner qr = new QueryRunner();
            //执行SQL语句
            String sql = "SELECT * FROM zhangwu WHERE id=?";
            Object[] params = {1};
            Connection conn = JDBCUtils.getConnection();
            ZhangWu zw = qr.query(conn, sql, new BeanHandler<ZhangWu>(ZhangWu.class), params);
            //结果集处理
            System.out.println(zw);
            
            conn.close();
        } catch(SQLException e){
            throw new RuntimeException(e);
        }
    }
}

2、BeanListHandler ：将结果集中每一条记录封装到指定的javaBean中，将这些javaBean在封装到List集合中

public class BeanListHandlerDemo {
    @Test
    public void method(){
        try{
            //获取QueryRunner 
            QueryRunner qr = new QueryRunner();
            //执行SQL语句
            String sql = "SELECT * FROM zhangwu WHERE money>?";
            Object[] params = {2000};
            Connection conn = JDBCUtils.getConnection();
            List<ZhangWu> list = qr.query(conn, sql, new BeanListHandler<ZhangWu>(ZhangWu.class), params);
            //结果集处理
            for (ZhangWu zw : list) {
                System.out.println(zw);
            }
            
            conn.close();
        } catch(SQLException e){
            throw new RuntimeException(e);
        }
    }
}

ColumnListHandler与ScalarHandler查询:

1、ColumnListHandler：将结果集中指定的列的字段值，封装到一个List集合中

public class ColumnListHandlerDemo {
    @Test
    public void method(){
        try {
            //获取QueryRunner对象
            QueryRunner qr = new QueryRunner();
            //执行SQL语句
            String sql = "SELECT name FROM zhangwu WHERE money>?";
            Object[] params = {2000};
            Connection conn = JDBCUtils.getConnection();
            List<String> list = qr.query(conn, sql, new ColumnListHandler<String>(), params); 
            //结果集的处理
            for (String str : list) {
                System.out.println(str);
            }

            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

 2、ScalarHandler：它是用于单数据。例如select count(*) from 表操作。

public class ScalarHandlerDemo {
    @Test
    public void method(){
        try {
            //获取QueryRunner对象
            QueryRunner qr = new QueryRunner();
            
            //执行SQL语句
            String sql = "SELECT MAX(money) FROM zhangwu";
            Object[] params = {};
            Connection conn = JDBCUtils.getConnection();
            Double max = qr.query(conn, sql, new ScalarHandler<Double>(), params);
            //结果集的处理
            System.out.println("max=" + max);
            
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

 连接池

　　实际开发中“获得连接”或“释放资源”是非常消耗系统资源的两个过程，为了解决此类性能问题，通常情况我们采用连接池技术，来共享连接Connection。这样我们就不需要每次都创建连接、释放连接了，这些操作都交给了连接池。

用池来管理Connection，这样可以重复使用Connection。有了池，所以我们就不用自己来创建Connection，而是通过池来获取Connection对象。当使用完Connection后，调用Connection的close()方法也不会真的关闭Connection，而是把Connection“归还”给池。池就可以再利用这个Connection对象了。

DBCP连接池

　　DBCP也是一个开源的连接池，是Apache Common成员之一，在企业开发中也比较常见，tomcat内置的连接池。

 编写工具类：

连接数据库表的工具类, 采用DBCP连接池的方式来完成，Java中提供了一个连接池的规则接口 ：

　　DataSource : 它是java中提供的连接池，作为 DriverManager 工具的替代项。在DBCP包中提供了DataSource接口的实现类，我们要用的具体的连接池 BasicDataSource类

public class JDBCUtils {
    public static final String DRIVER = "com.mysql.jdbc.Driver";
    public static final String URL = "jdbc:mysql://localhost:3306/daydb";
    public static final String USERNAME = "root";
    public static final String PASSWORD = "root";
    /*
     * 创建连接池BasicDataSource
     */
    public static BasicDataSource dataSource = new BasicDataSource();
    //静态代码块
    static {
        //对连接池对象 进行基本的配置
        dataSource.setDriverClassName(DRIVER); // 这是要连接的数据库的驱动
        dataSource.setUrl(URL); //指定要连接的数据库地址
        dataSource.setUsername(USERNAME); //指定要连接数据的用户名
        dataSource.setPassword(PASSWORD); //指定要连接数据的密码
    }
    /*
     * 返回连接池对象
     */
    public static DataSource getDataSource(){
        return dataSource;
    }
}

/*
 * 演示使用DBUtils工具  完成数据库表的增加操作
 */
public class Demo {
    // 插入功能 
    @Test
    public void insert(){
        try {
            //获取一个用来执行SQL语句的对象   QueryRunner
            QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
String sql = "INSERT INTO zhangwu(name,money,parent) VALUES(?,?,?)";
            Object[] params = {"股票收入", 5500, "收入"};    
             int line = qr.update(sql,params);
            //结果集处理
            System.out.println("line = " + line);
            
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    
    //删除功能
    @Test
    public void delete(){
        try {
            //创建一个QueryRunner对象，用来完成SQL语句的执行
            QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
            //执行SQL语句
            String sql = "DELETE FROM zhangwu WHERE name = ?";
            Object[] params = {"股票收入"};
            int line = qr.update(sql, params);
            //结果集的处理
            System.out.println("line="+line);
            
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    
    //更新功能
    @Test
    public void update(){
        try {
            //创建一个QueryRunner对象，用来完成SQL语句的执行
            QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
            //执行SQL语句
            String sql = "UPDATE zhangwu SET money = money+1000 WHERE name=?";
            Object[] params = {"股票收入"};
            int line = qr.update(sql, params);
            //结果集的处理
            System.out.println("line="+line);
            
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    
    //查询功能,将结果集中第一条记录封装到一个指定的javaBean中。
    @Test
    public void search(){
        try{
            //获取QueryRunner 
            QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
            //执行SQL语句
            String sql = "SELECT * FROM zhangwu";
            Object[] params = {};
            Product p = qr.query(sql, new BeanHandler<Product>(Product.class), params);
            //结果集处理
            System.out.println(p);
            
        } catch(SQLException e){
            throw new RuntimeException(e);
        }
    }
}

 常见配置项

分类	属性	描述
必须项	driverClassName	数据库驱动名称
	url	数据库的地址
	username	用户名
	password	密码
基本项（扩展）	maxActive	最大连接数量
	minIdle	最小空闲连接
	maxIdle	最大空闲连接
	initialSize	初始化连接

你好