JDBC

MySQL常用命令

1. create database db_name;
2. show databases;
3. drop database db_name;
4. use db_name;
5. select database();
6. create table tab_name (
   字段名 类型(长度) [约束],
   字段名 类型(长度) [约束]
   );
   create table user (
   id int primary key auto_increment,
   username varchar(32),
   password varchar(32)
   );
7. show tables;
8. desc tab_name;
9. drop table tab_name;
10. rename table tab_name to new_name;
11. insert into tab_name(col name...) values(value...);
12. select * from tab_name;
13. update tab_name set col=value where 条件;
14. delete from tab_name where 条件;

JDBC准备

1. 下载mysql-connector-java.jar
2. 导入并构建jar包
3. 导入MySQL驱动，com.mysql.jdbc.Driver

JDBC创建

注意：最好用try...catch...finally代码块包裹

1. 加载数据库驱动 Class.forName("com.mysql.jdbc.Driver");
2. 创建数据库连接

String url = "jdbc:mysql://IP地址:端口号/数据库?useUnicode&characterEncoding=uft-8";
String user = "root";
String password = "root";
//创建连接对象 
Connection connection = DriverManager.getConnection(url, user, password);

3. 向数据库发起增删查改的请求

//创建执行对象
Statement statement = connection.createStatement();
//创建结果集对象，将结果存到结果集中
ResultSet resultSet = statement.executeQuery("select * from user");
//单行查询
while (resultSet.next()) {
  System.out.println(resultSet.getInt(1) + " " + resultSet.getString(2) + " " +  resultSet.getString(3));
}

4. 关闭数据库连接

if (resultSet != null) {
  try {
        resultSet.close();
  } catch (SQLException e) {
      e.printStackTrace();
  }
}
if (statement != null) {
  try {
        statement.close();
  } catch (SQLException e) {
      e.printStackTrace();
  }
}
if (connection != null) {
  try {
        connection.close();
  } catch (SQLException e) {
      e.printStackTrace();
  }
}

sql注入问题

    public class Demo02 {
        public static void main(String[] args) {
            Demo02 demo02 = new Demo02();
            //注意这里所传入的参数 如果是or '1' = '1' 条件则是正确的
            System.out.println(demo02.verify("zxc", "1234 ' or '1' = '1"));
     }

    public boolean verify(String username_c, String password_c) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/web01?useUnicode&characterEncoding=utf-8";
            String username = "root";
            String password = "iloveyou";
            connection = DriverManager.getConnection(url, username, password);
            statement = connection.createStatement();
            String sql = "select * from user where username = '" + username_c + "' and password = '" + password_c + "';";
            resultSet = statement.executeQuery(sql);
            if (resultSet.next()) {
                return true;
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return false;
    }
}

这段代码只要密码含有or '1' = '1'，则会通过验证
为了解决sql注入问题，我们一般用PerparStatement作为执行对象

public boolean verify(String username_c, String password_c) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/web01?useUnicode&characterEncoding=utf-8";
            String username = "root";
            String password = "iloveyou";
            connection = DriverManager.getConnection(url, username, password);
            String sql = "select * from user where username = ? and password = ?";
            preparedStatement = connection.prepareStatement(sql);
            //设置？的参数
            preparedStatement.setString(1, username_c);
            preparedStatement.setString(2, password_c);
            //上面已经执行过sql这里不用执行
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                return true;
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return false;
    }
}

用PreparedStatement就可以解决sql注入问题

PreparedStatement与Statement的区别

转自Statement与PreparedStatement的区别 - 无措 - 博客园

1. PreparedStatement是预编译的，比Statement执行速度快
   每一种数据库都会尽最大努力对预编译语句提供最大的性能优化；因为预编译语句有可能被重复调用.所以语句在被DB的编译器编译后的执行代码被缓存下来，那么下次调用时只要是相同的预编译语句就不需要编译，只要将参数直接传入编译过的语句执行代码中(相当于一个函数)就会得到执行；这并不是说只有一个Connection中多次执行的预编译语句被缓存，而是对于整个DB中，只要预编译的语句语法和缓存中匹配，那么在任何时候就可以不需要再次编译而可以直接执行；而statement的语句中，即使是相同一操作，而由于每次操作的数据不同所以使整个语句相匹配的机会极小，几乎不太可能匹配。比如：
   insert into tb_name (col1,col2) values ('11','22');
   insert into tb_name (col1,col2) values ('11','23');
   即使是相同操作但因为数据内容不一样,所以整个个语句本身不能匹配,没有缓存语句的意义.事实是没有数据库会对普通语句编译后的执行代码缓存。
   当然并不是所有预编译语句都一定会被缓存,数据库本身会用一种策略,比如使用频度等因素来决定什么时候不再缓存已有的预编译结果.以保存有更多的空间存储新的预编译语句。
2. 代码的可读性和可维护性
   虽然用PreparedStatement来代替Statement会使代码多出几行,但这样的代码无论从可读性还是可维护性上来说.都比直接用Statement的代码高很多档次：
   stmt.executeUpdate("insert into tb_name (col1,col2,col2,col4) values ('"+var1+"','"+var2+"',"+var3+",'"+var4+"')");
   perstmt = con.prepareStatement("insert into tb_name (col1,col2,col2,col4) values (?,?,?,?)");
   perstmt.setString(1,var1);
   perstmt.setString(2,var2);
   perstmt.setString(3,var3);
   perstmt.setString(4,var4);
   perstmt.executeUpdate();
   不用我多说,对于第一种方法，别说其他人去读你的代码，就是你自己过一段时间再去读,都会觉得伤心。
3. 安全性
   PreparedStatement可以防止SQL注入攻击，而Statement却不能。比如说：
   String sql = "select * from tb_name where name= '"+varname+"' and passwd='"+varpasswd+"'";
   如果我们把[' or '1' = '1]作为varpasswd传入进来.用户名随意,看看会成为什么?
   select * from tb_name = '随意' and passwd = '' or '1' = '1';
   因为'1'='1'肯定成立，所以可以任何通过验证，更有甚者：
   把[';drop table tb_name;]作为varpasswd传入进来,则：
   select * from tb_name = '随意' and passwd = '';drop table tb_name;有些数据库是不会让你成功的，但也有很多数据库就可以使这些语句得到执行。
   而如果你使用预编译语句你传入的任何内容就不会和原来的语句发生任何匹配的关系，只要全使用预编译语句你就用不着对传入的数据做任何过虑。而如果使用普通的statement,有可能要对drop等做费尽心机的判断和过虑。

分页查询

select * from 表名 limit [位置偏移量], 行数
页是(查询页数-1)*行数, 行数

public class Demo03 {
    public static void main(String[] args) {
        Demo03 demo03 = new Demo03();
        demo03.pageQuery(2, 4);
    }

    public void pageQuery(int pagenum, int linenum) {
        pagenum = (pagenum - 1) * linenum;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/web01?useUnicode=true&CharacterEncoding=utf-8";
            String username = "root";
            String password = "iloveyou";
            connection = DriverManager.getConnection(url, username, password);
            String sql = "select * from user limit ?, ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, pagenum);
            preparedStatement.setInt(2, linenum);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                System.out.println(resultSet.getInt(1) + " " + resultSet.getString(2) + " " + resultSet.getString(3));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

CRUD

create read update delete
DML语言使用preparedStatement.executeUpdate();
preparedStatement.executeUpdate返回值 操作成功返回操作的行数，操作失败返回0

事务操作

事物的开启：connection.setAutoCommit(false); //设置false之后，为手动提交
使徒的提交：connection.commit();
将所执行的sql代码放在其中

连接池

1. 什么是连接池呢？

转自什么是数据库连接池

数据库的连接是非常耗费系统资源的，一个应用通常都是需要与数据库打交道，也就需要经常连接数据库，这样导致浪费大量系统资源；
连接池的原理就是：我事先创建好几个数据库连接放着，当我的系统需要操作数据库时就从连接池里直接拿连接，并将这个连接标记为 忙 ；用完后在放会池中，标记为 空闲；
当连接池里的连接都在被使用，如果此时还要连接，连接池就会在创建连接放到池里，这些连接的数量，都是在配置文件里由你控制的
2. 如何创建数据库连接池？
1）建立数据库对象（开启服务器）
2）按照事前制定的参数创建初始数量的数据库连接（空闲连接）
3）当得到获取连接请求时，直接从连接池中得到一个连接，如果当前连接池没有空闲连接，则创建一个新的连接给它
4）对数据库进行操作
5）关闭数据库，释放数据库连接给连接池（这里的关闭并非真正的关闭，而是将其放入到空闲队列中）
6）释放数据库连接池对象（关闭服务器）

第三方连接池(或者叫数据源)插件dbcp c3p0

1. 下载插件 找到commons-dbcp2-2.7.0.jar导入并构建 还需下载commons pool和commons logging因为dbcp等需要这两个包的支持
2. dbcp使用
   依赖commons pool和commons logging

public class Demo04 {
    private static String url = "jdbc:mysql://localhost:3306/web01?useUnicode=true&characterEncoding=utf-8";
    private static String username = "root";
    private static String password = "iloveyou";
    private static BasicDataSource basicDataSource;
    static {
        //获取数据源对象
        basicDataSource = new BasicDataSource();
        //加载驱动
        basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
        //设置连接
        basicDataSource.setUrl(url);
        basicDataSource.setUsername(username);
        basicDataSource.setPassword(password);
        //设置初始连接
        basicDataSource.setInitialSize(5);
        //设置最大连接 服务器承载的最大数
        basicDataSource.setMaxTotal(20);
        //设置空闲连接 防止有其他人进入时还得创建连接
        basicDataSource.setMinIdle(3);
    }

    public static Connection getConnection() {
        try {
            return basicDataSource.getConnection(); //调用者可以直接close，该插件不会受影响(不会关闭)
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}

3. c3p0使用
   依赖mchange commons
   使用跟dbcp相似

遇到的bug

1. java.lang.SecurityException: Prohibited package name: java.test 不能用java开头作为包名
2. jdbc java.net.UnknownHostException: localhost.3306 应该是localhost:3306