当前位置： 首页 > article >正文

MySQL之JDBC入门详解

article 2024/12/23 15:13:40

01-JDBC入门 

一、JDBC概念

    jdbc : java database connection , java数据库连接

    jdbc是sun公司定义的java程序访问数据库的规范。

二、JDBC操作需要6步

三、入门程序

    1、使用eclipse打开一个新的工作空间

    2、切换到java视图界面

    3、创建java工程：01-jdbc-helloworld

    4、添加mysql驱动包，并解压

    5、创建数据库表：t_user 用户表

    6、创建java包和测试类：UserTest

package com.youzhong.test;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Date;

public class UserTest {

    public static void main(String[] args) {

        Connection conn = null;

        Statement st = null;

        ResultSet rs = null;

        try {

            // 1、注册驱动

            // 包名+类名 = 全限定名

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

            // 2、创建数据库连接

            // url：数据库连接地址

            // username : 帐号

            // password : 密码

            conn =  DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "root");

            // 3、创建SQL执行对象（货车）

            st = conn.createStatement();

            // 4、执行SQL

            rs = st.executeQuery("select * from  t_user");

            // 5、处理结果集

            // ResultSet结果集中有一个游标，刚开始指向第一条记录之前，调用一次next()方法，游标指向下一条记录。

            // 如果next()调用后指向了有效记录，返回true，如果next()调用后没有指向记录，返回false.

            while (rs.next()) {

                // 获取游标指向的这条记录的数据

                // 根据类型获取数据

                int id = rs.getInt(1);// 根据列号获取数据

                // int id = rs.getInt("id");//根据列名获取数据

                String name = rs.getString(2);

                Date birthday = rs.getDate(3);

                System.out.println(id + "\t" +  name + "\t" + birthday);

            }

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            // 6、释放资源  Connection,Statement,ResultSet

            // 资源要保证一定能释放，放在finnaly块中

            try {

                conn.close();

                st.close();

                rs.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

}

02-JDBC-API 

一、什么是API

    API是应用程序接口。

    java的API文档

    API其实就是定义了类，接口，及属性，方法等信息。

二、JDBC的4个API

    java.sql包下的

    1、DriverManager 类

    2、Connection 接口

    3、Statement 接口

    4、ResultSet 接口

03-JDBC的六个步骤 

一、注册驱动

    把驱动类注册到内存中。

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

二、创建连接

    Connection代表了数据库连接。

    一个Connection对象就相当于打开了一个数据库连接的CMD窗口。

Connection conn = DriverManager.getConnection(url,username,password);

    url是数据库连接地址： jdbc:mysql://127.0.0.1:3306/test

    

     地址可以简写成：jdbc:mysql:///test ，ip默认是本机，端口默认是3306。

    username是数据库连接的帐号

    password是数据库连接的密码

三、创建SQL执行对象

    Statement是SQL执行对象。

    Statement作用是把SQL从java程序中运送到数据库中执行。相当于一辆货车。

Statement st = conn.createStatement();

四、执行SQL

    执行SQL才是真正的开始运送过程。

ResultSet rs = st.executeQuery(String sql); //select查询语句调用此方法

int count = st.executeUpdate(String sql);//insert,delete,update增删改的语句

//返回的count是增删改语句影响了几条记录

五、处理结果集

    ResultSet是结果集。

    ResultSet结果集内部有一个游标，游标用来指向某一条记录。

    最开始游标指向第一条前面。调用一次rs.next()方法，游标就指向下一条记录。

    如果游标指向了有效记录，next()返回true。如果指到了结果集最后，没有记录了，返回false。

    

while(rs.next()){

    //获取当前记录的数据

    //通过getInt(),getString(),getDate(),getDouble()等方法获取每一列数据

    rs.getXxx(int 列号); // 第1列的列号是1，第2列是2，依此类推

    rs.getXxx(String 列名);

}

    常用的数据库类型与getXxx()方法对照表：

    getDate()只能返回年月日，getTimestamp()可以返回年月日 时分秒。

    getDate()，getTime(), getTimestamp()默认返回的类型全是 java.sql 包下的。

    但是我们可以统一使用 java.util.Date 来接收。（SimpleDateFormat）

六、释放资源

    Connection , Statement , ResultSet 是连接着数据库的资源。

    如果数据库访问完成后资源不释放，将会影响电脑的性能，也影响别人的访问。

    必须释放所有资源。使用 finnally块 可以保证一定释放资源。    

finnally{

    try{

        conn.close();

        st.close();

        rs.close();

    }catch(Exception e){

        e.printStackTrace();

    }

}

04-JDBC-CRUD 

一、什么是CRUD

    CRUD是增删改查，但不仅仅是增删改查。

    除了：查询列表，添加一条，修改一条，删除一条。

    还包括：模糊查询，批量删除，分页查询，查询总数。

二、JDBC完成用户的CRUD

package com.youzhong.test;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Date;

/**

* 用户的CRUD操作

*

* @author liwei

*

*/

public class UserCRUDTest {

    public static void main(String[] args) {

        

        UserCRUDTest test = new UserCRUDTest();

        

        //1、测试列表

        //test.list();

        

        //2、添加记录

        //test.insert();

        

        //3、修改记录

        //test.update();

        

        //4、删除记录

        //test.delete();

        

        //5、模糊查询

        //test.findNameLike();

        

        //6、批量删除

        //test.deletes();

        

        //7、分页查询

        //test.fenye();

        

        //8、查总数

        test.getTotal();

    }

    // 1、查询列表

    public void list() {

        Connection conn = null;

        Statement st = null;

        ResultSet rs = null;

        try {

            // 1、注册驱动

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

            // 2、创建Connection连接

            conn =  DriverManager.getConnection("jdbc:mysql:///test", "root", "root");

            // 3、创建Statement对象

            st = conn.createStatement();

            // 4、执行SQL

            rs = st.executeQuery("select * from  t_user");

            // 5、处理结果集

            while (rs.next()) {

                int id = rs.getInt(1);

                String name = rs.getString(2);

                Date birthday = rs.getDate(3);

                System.out.println(id + "\t" +  name + "\t" + birthday);

            }

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            // 6、释放资源

            try {

                conn.close();

                st.close();

                rs.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

    // 2、添加记录

    public void insert() {

        Connection conn = null;

        Statement st = null;

        try {

            // 1、注册驱动

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

            // 2、创建Connection连接

            conn =  DriverManager.getConnection("jdbc:mysql:///test", "root", "root");

            // 3、创建Statement对象

            st = conn.createStatement();

            // 4、执行SQL

            int count = st.executeUpdate("insert  into t_user values (null,'张三','2018-08-08')");

            // 5、处理结果集

            System.out.println("添加了"+count+"条记录");

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            // 6、释放资源

            try {

                conn.close();

                st.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

    // 3、修改记录

    public void update() {

        Connection conn = null;

        Statement st = null;

        try {

            // 1、注册驱动

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

            // 2、创建Connection连接

            conn =  DriverManager.getConnection("jdbc:mysql:///test", "root", "root");

            // 3、创建Statement对象

            st = conn.createStatement();

            // 4、执行SQL

            int count = st.executeUpdate("update  t_user set name='杨幂',birthday='2019-05-05'  where id = 9");

            // 5、处理结果集

            System.out.println("修改了"+count+"条记录");

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            // 6、释放资源

            try {

                conn.close();

                st.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

    // 4、删除记录

    public void delete() {

        Connection conn = null;

        Statement st = null;

        try {

            // 1、注册驱动

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

            // 2、创建Connection连接

            conn =  DriverManager.getConnection("jdbc:mysql:///test", "root", "root");

            // 3、创建Statement对象

            st = conn.createStatement();

            // 4、执行SQL

            int count = st.executeUpdate("delete  from t_user where id = 9");

            // 5、处理结果集

            System.out.println("删除了"+count+"条记录");

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            // 6、释放资源

            try {

                conn.close();

                st.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

    // 5、模糊查询

    public void findNameLike() {

        Connection conn = null;

        Statement st = null;

        ResultSet rs = null;

        try {

            // 1、注册驱动

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

            // 2、创建Connection连接

            conn =  DriverManager.getConnection("jdbc:mysql:///test", "root", "root");

            // 3、创建Statement对象

            st = conn.createStatement();

            // 4、执行SQL

            rs = st.executeQuery("select * from  t_user where name like 'x%'");

            // 5、处理结果集

            while (rs.next()) {

                int id = rs.getInt(1);

                String name = rs.getString(2);

                Date birthday = rs.getDate(3);

                System.out.println(id + "\t" +  name + "\t" + birthday);

            }

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            // 6、释放资源

            try {

                conn.close();

                st.close();

                rs.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

    // 6、批量删除

    public void deletes() {

        Connection conn = null;

        Statement st = null;

        try {

            // 1、注册驱动

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

            // 2、创建Connection连接

            conn =  DriverManager.getConnection("jdbc:mysql:///test", "root", "root");

            // 3、创建Statement对象

            st = conn.createStatement();

            // 4、执行SQL

            int count = st.executeUpdate("delete  from t_user where id in (4,6,8)");

            // 5、处理结果集

            System.out.println("删除了"+count+"条记录");

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            // 6、释放资源

            try {

                conn.close();

                st.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

    // 7、分页查询

    public void fenye() {

        Connection conn = null;

        Statement st = null;

        ResultSet rs = null;

        try {

            // 1、注册驱动

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

            // 2、创建Connection连接

            conn =  DriverManager.getConnection("jdbc:mysql:///test", "root", "root");

            // 3、创建Statement对象

            st = conn.createStatement();

            // 4、执行SQL

            //每页2条

            //第1页 ：0,2

            //第2页：2,2

            //第3页：4，2

            //6，2

            rs = st.executeQuery("select * from  t_user limit 4,2");

            // 5、处理结果集

            while (rs.next()) {

                int id = rs.getInt(1);

                String name = rs.getString(2);

                Date birthday = rs.getDate(3);

                System.out.println(id + "\t" +  name + "\t" + birthday);

            }

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            // 6、释放资源

            try {

                conn.close();

                st.close();

                rs.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

    // 8、查询总数

    public void getTotal() {

        Connection conn = null;

        Statement st = null;

        ResultSet rs = null;

        try {

            // 1、注册驱动

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

            // 2、创建Connection连接

            conn =  DriverManager.getConnection("jdbc:mysql:///test", "root", "root");

            // 3、创建Statement对象

            st = conn.createStatement();

            // 4、执行SQL

            rs = st.executeQuery("select  count(*) from t_user");

            // 5、处理结果集

            rs.next();

            int total = rs.getInt(1);

            System.out.println("一共有"+total+"条记录");

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            // 6、释放资源

            try {

                conn.close();

                st.close();

                rs.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

}

05-JDBC优化 

一、给CRUD的方法添加参数

    

/**

* 新闻的CRUD操作

*

* @author liwei

*

*/

public class NewsCRUDTest {

    

    public static void main(String[] args) {

        NewsCRUDTest newsTest = new  NewsCRUDTest();

        

        //1、测试列表功能

        //newsTest.list();

        

        //2、添加功能

        //newsTest.insert("濮阳小伙",new  Date(),"濮阳小伙当上了非洲酋长");

        

        //3、修改功能

        //newsTest.update(2,"端午三天",new  Date(),"高速不免费");

        

        //4、删除记录

        newsTest.delete(6);

    }

    // 1、查看列表

    public void list() {

        Connection conn = null;

        Statement st = null;

        ResultSet rs = null;

        try {

            // 1、注册驱动

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

            // 2、创建连接

            conn =  DriverManager.getConnection("jdbc:mysql:///test", "root", "root");

            // 3、创建Sql执行对象

            st = conn.createStatement();

            // 4、执行SQL

            rs = st.executeQuery("select * from  t_news");

            // 5、处理结果集

            while (rs.next()) {

                int id = rs.getInt(1);

                String title = rs.getString(2);

                Date publishTime =  rs.getTimestamp(3);

                //用SimpleDateFormat对java.util.Date进行格式化

                SimpleDateFormat sdf = new  SimpleDateFormat("yyyy/MM/dd HH:mm:ss");

                String content =  rs.getString(4);

                System.out.println(id + "\t" +  title + "\t" + sdf.format(publishTime) + "\t" +  content);

            }

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            // 6、释放资源

            try {

                conn.close();

                st.close();

                rs.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

    // 2、添加记录

    public void insert(String title,Date  publishTime,String content) {

        Connection conn = null;

        Statement st = null;

        try {

            // 1、注册驱动

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

            // 2、创建连接

            conn =  DriverManager.getConnection("jdbc:mysql:///test", "root", "root");

            // 3、创建Sql执行对象

            st = conn.createStatement();

            // 4、执行SQL

            SimpleDateFormat sdf = new  SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            String sql = "insert into t_news  values  (null,'"+title+"','"+sdf.format(publishTime)+"','"+content+"')";

            System.out.println(sql);

            int count = st.executeUpdate(sql);

            // 5、处理结果集

            System.out.println("添加了"+count+"条记录");

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            // 6、释放资源

            try {

                conn.close();

                st.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

    // 3、修改记录

    public void update(int id,String title,Date  publishTime,String content) {

        Connection conn = null;

        Statement st = null;

        try {

            // 1、注册驱动

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

            // 2、创建连接

            conn =  DriverManager.getConnection("jdbc:mysql:///test", "root", "root");

            // 3、创建Sql执行对象

            st = conn.createStatement();

            // 4、执行SQL

            SimpleDateFormat sdf = new  SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            int count = st.executeUpdate("update  t_news set  title='"+title+"',publish_time='"+sdf.format(publishTime)+"',content='"+content+"' where id =  "+id);

            // 5、处理结果集

            System.out.println("修改了"+count+"条记录");

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            // 6、释放资源

            try {

                conn.close();

                st.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

    // 4、删除记录

    public void delete(int id) {

        Connection conn = null;

        Statement st = null;

        try {

            // 1、注册驱动

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

            // 2、创建连接

            conn =  DriverManager.getConnection("jdbc:mysql:///test", "root", "root");

            // 3、创建Sql执行对象

            st = conn.createStatement();

            // 4、执行SQL

            int count = st.executeUpdate("delete  from t_news where id = "+id);

            // 5、处理结果集

            System.out.println("删除了"+count+"条记录");

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            // 6、释放资源

            try {

                conn.close();

                st.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

}

二、定义一个实体类，封装方法的参数，封装列表方法的返回数据

    实体类：项目用到的对应现实世界中物体的类。

                 学生  -> Student类

                 新闻  -> News类

                 商品  -> Goods类

                 订单  -> Order类

                 每一张表对应创建的一个类就是实体类（xxx.xxx.entity）。

                 表  ->  类

                 列  ->  属性

    根据t_news新闻表，就要创建 News 新闻类，放在com.youzhong.entity包下。

public class News {

    private int id;

    private String title;

    private Date publishTime;

    private String content;

    //set , get 方法

    //全参，无参构造方法

    //toString方法

package com.youzhong.test;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

import com.youzhong.entity.News;

/**

* 新闻的CRUD操作

*

* @author liwei

*

*/

public class NewsCRUDTest {

    

    public static void main(String[] args) {

        NewsCRUDTest newsTest = new  NewsCRUDTest();

        

        //1、测试列表功能

        List<News> list = newsTest.list();

        for (News news : list) {

            System.out.println(news);

        }

        

        //2、添加功能

        //newsTest.insert(new News(0,"没啥新闻了",new Date(),"xxx"));

        

        //3、修改功能

        //newsTest.update(new News(7,"还有一条新闻",new Date(),"王老二家的猪丢了"));

        

        //4、删除记录

        //newsTest.delete(6);

    }

    // 1、查看列表

    public List<News> list() {

        List<News> list = new ArrayList<News>();

        Connection conn = null;

        Statement st = null;

        ResultSet rs = null;

        try {

            // 1、注册驱动

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

            // 2、创建连接

            conn =  DriverManager.getConnection("jdbc:mysql:///test", "root", "root");

            // 3、创建Sql执行对象

            st = conn.createStatement();

            // 4、执行SQL

            rs = st.executeQuery("select * from  t_news");

            // 5、处理结果集

            while (rs.next()) {

                int id = rs.getInt(1);

                String title = rs.getString(2);

                Date publishTime =  rs.getTimestamp(3);

                //用SimpleDateFormat对java.util.Date进行格式化

                //SimpleDateFormat sdf = new  SimpleDateFormat("yyyy/MM/dd HH:mm:ss");

                String content =  rs.getString(4);

                //System.out.println(id + "\t" +  title + "\t" + sdf.format(publishTime) + "\t" +  content);

                //把数据封装成News对象

                News news = new  News(id,title,publishTime,content);

                //再把news对象添加到list集合中

                list.add(news);

            }

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            // 6、释放资源

            try {

                conn.close();

                st.close();

                rs.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

        return list;

    }

    // 2、添加记录

    public void insert(News news) {

        Connection conn = null;

        Statement st = null;

        try {

            // 1、注册驱动

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

            // 2、创建连接

            conn =  DriverManager.getConnection("jdbc:mysql:///test", "root", "root");

            // 3、创建Sql执行对象

            st = conn.createStatement();

            // 4、执行SQL

            SimpleDateFormat sdf = new  SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

          String sql = "insert into t_news  values (null,'"+news.getTitle()+"','"+sdf.format(news.getPublishTime())+"','"+news.getContent()+"')";

            System.out.println(sql);

            int count = st.executeUpdate(sql);

            // 5、处理结果集

            System.out.println("添加了"+count+"条记录");

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            // 6、释放资源

            try {

                conn.close();

                st.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

    // 3、修改记录

    public void update(News news) {

        Connection conn = null;

        Statement st = null;

        try {

            // 1、注册驱动

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

            // 2、创建连接

            conn =  DriverManager.getConnection("jdbc:mysql:///test", "root", "root");

            // 3、创建Sql执行对象

            st = conn.createStatement();

            // 4、执行SQL

            SimpleDateFormat sdf = new  SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            int count = st.executeUpdate("update  t_news set  title='"+news.getTitle()+"',publish_time='"+sdf.format(news.getPublishTime())+"',content='"+news.getContent()+"' where id = "+news.getId());

            // 5、处理结果集

            System.out.println("修改了"+count+"条记录");

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            // 6、释放资源

            try {

                conn.close();

                st.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

    // 4、删除记录

    public void delete(int id) {

        Connection conn = null;

        Statement st = null;

        try {

            // 1、注册驱动

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

            // 2、创建连接

            conn =  DriverManager.getConnection("jdbc:mysql:///test", "root", "root");

            // 3、创建Sql执行对象

            st = conn.createStatement();

            // 4、执行SQL

            int count = st.executeUpdate("delete  from t_news where id = "+id);

            // 5、处理结果集

            System.out.println("删除了"+count+"条记录");

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            // 6、释放资源

            try {

                conn.close();

                st.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

}

三、把资源的定义提升为属性

    

package com.youzhong.test;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

import com.youzhong.entity.News;

/**

* 新闻的CRUD操作

*

* @author liwei

*

*/

public class NewsCRUDTest {

    

    private Connection conn = null;

    private Statement st = null;

    private ResultSet rs = null;

    

    public static void main(String[] args) {

        NewsCRUDTest newsTest = new  NewsCRUDTest();

        

        //1、测试列表功能

        List<News> list = newsTest.list();

        for (News news : list) {

            System.out.println(news);

        }

        

        //2、添加功能

        //newsTest.insert(new News(0,"没啥新闻了",new Date(),"xxx"));

        

        //3、修改功能

        //newsTest.update(new News(7,"还有一条新闻",new Date(),"王老二家的猪丢了"));

        

        //4、删除记录

        //newsTest.delete(6);

    }

    // 1、查看列表

    public List<News> list() {

        List<News> list = new ArrayList<News>();

        try {

            // 1、注册驱动

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

            // 2、创建连接

            conn =  DriverManager.getConnection("jdbc:mysql:///test", "root", "root");

            // 3、创建Sql执行对象

            st = conn.createStatement();

            // 4、执行SQL

            rs = st.executeQuery("select * from  t_news");

            // 5、处理结果集

            while (rs.next()) {

                int id = rs.getInt(1);

                String title = rs.getString(2);

                Date publishTime =  rs.getTimestamp(3);

                //用SimpleDateFormat对java.util.Date进行格式化

                //SimpleDateFormat sdf = new  SimpleDateFormat("yyyy/MM/dd HH:mm:ss");

                String content =  rs.getString(4);

                //System.out.println(id + "\t" +  title + "\t" + sdf.format(publishTime) + "\t" +  content);

                //把数据封装成News对象

                News news = new  News(id,title,publishTime,content);

                //再把news对象添加到list集合中

                list.add(news);

            }

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            // 6、释放资源

            try {

                conn.close();

                st.close();

                rs.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

        return list;

    }

    // 2、添加记录

    public void insert(News news) {

        try {

            // 1、注册驱动

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

            // 2、创建连接

            conn =  DriverManager.getConnection("jdbc:mysql:///test", "root", "root");

            // 3、创建Sql执行对象

            st = conn.createStatement();

            // 4、执行SQL

            SimpleDateFormat sdf = new  SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            String sql = "insert into t_news  values  (null,'"+news.getTitle()+"','"+sdf.format(news.getPublishTime())+"','"+news.getContent()+"')";

            System.out.println(sql);

            int count = st.executeUpdate(sql);

            // 5、处理结果集

            System.out.println("添加了"+count+"条记录");

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            // 6、释放资源

            try {

                conn.close();

                st.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

    // 3、修改记录

    public void update(News news) {

        try {

            // 1、注册驱动

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

            // 2、创建连接

            conn =  DriverManager.getConnection("jdbc:mysql:///test", "root", "root");

            // 3、创建Sql执行对象

            st = conn.createStatement();

            // 4、执行SQL

            SimpleDateFormat sdf = new  SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            int count = st.executeUpdate("update  t_news set  title='"+news.getTitle()+"',publish_time='"+sdf.format(news.getPublishTime())+"',content='"+news.getContent()+"' where id = "+news.getId());

            // 5、处理结果集

            System.out.println("修改了"+count+"条记录");

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            // 6、释放资源

            try {

                conn.close();

                st.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

    // 4、删除记录

    public void delete(int id) {

        try {

            // 1、注册驱动

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

            // 2、创建连接

            conn =  DriverManager.getConnection("jdbc:mysql:///test", "root", "root");

            // 3、创建Sql执行对象

            st = conn.createStatement();

            // 4、执行SQL

            int count = st.executeUpdate("delete  from t_news where id = "+id);

            // 5、处理结果集

            System.out.println("删除了"+count+"条记录");

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            // 6、释放资源

            try {

                conn.close();

                st.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

}

四、封装一个DBUtil工具类，减少重复性代码。

    

//数据库工具类

//封装重复性代码

public class DBUtil {

    // 1、获取数据库连接

    public static Connection getConnection() {

        try {

            // 1、注册驱动 

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

            // 2、创建连接

            Connection conn =  DriverManager.getConnection("jdbc:mysql:///test", "root", "root");

            return conn;

        } catch (Exception e) {

            e.printStackTrace();

        }

        return null;

    }

    // 2、释放资源

    public static void close(Connection  conn,Statement st,ResultSet rs) {

        try {

            if(conn!=null) {

                conn.close();

            }

            if(st!=null) {

                st.close();

            }

            if(rs!=null) {

                rs.close();

            }

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

}

package com.youzhong.test;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

import com.youzhong.entity.News;

import com.youzhong.util.DBUtil;

/**

* 新闻的CRUD操作

*

* @author liwei

*

*/

public class NewsCRUDTest {

     

     private Connection conn = null;

     private Statement st = null;

     private ResultSet rs = null;

     

     public static void main(String[] args) {

          NewsCRUDTest newsTest = new NewsCRUDTest();

          

          //1、测试列表功能

//        List<News> list = newsTest.list();

//        for (News news : list) {

//            System.out.println(news);

//        }

          

          //2、添加功能

          newsTest.insert(new News(0,"没啥新闻了",new  Date(),"xxx"));

          

          //3、修改功能

          //newsTest.update(new News(7,"还有一条新闻",new  Date(),"王老二家的猪丢了"));

          

          //4、删除记录

          //newsTest.delete(6);

     }

     // 1、查看列表

     public List<News> list() {

          List<News> list = new ArrayList<News>();

          try {

              conn = DBUtil.getConnection();

              // 3、创建Sql执行对象

              st = conn.createStatement();

              // 4、执行SQL

              rs = st.executeQuery("select * from  t_news");

              // 5、处理结果集

              while (rs.next()) {

                   int id = rs.getInt(1);

                   String title = rs.getString(2);

                   Date publishTime = rs.getTimestamp(3);

                   //用SimpleDateFormat对java.util.Date进行格式化

                   //SimpleDateFormat sdf = new  SimpleDateFormat("yyyy/MM/dd HH:mm:ss");

                   String content = rs.getString(4);

                   //System.out.println(id + "\t" + title  + "\t" + sdf.format(publishTime) + "\t" + content);

                   //把数据封装成News对象

                   News news = new  News(id,title,publishTime,content);

                   //再把news对象添加到list集合中

                   list.add(news);

              }

          } catch (Exception e) {

              e.printStackTrace();

          } finally {

              // 6、释放资源

              DBUtil.close(conn, st, rs);

          }

          return list;

     }

     // 2、添加记录

     public void insert(News news) {

          try {

              conn = DBUtil.getConnection();

              // 3、创建Sql执行对象

              st = conn.createStatement();

              // 4、执行SQL

              SimpleDateFormat sdf = new  SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

              String sql = "insert into t_news values  (null,'"+news.getTitle()+"','"+sdf.format(news.getPublishTime())+"','"+news.getContent()+"')";

              System.out.println(sql);

              int count = st.executeUpdate(sql);

              // 5、处理结果集

              System.out.println("添加了"+count+"条记录");

          } catch (Exception e) {

              e.printStackTrace();

          } finally {

              // 6、释放资源

              DBUtil.close(conn,st,rs);

          }

     }

     // 3、修改记录

     public void update(News news) {

          try {

              conn = DBUtil.getConnection();

              // 3、创建Sql执行对象

              st = conn.createStatement();

              // 4、执行SQL

              SimpleDateFormat sdf = new  SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

              int count = st.executeUpdate("update t_news  set  title='"+news.getTitle()+"',publish_time='"+sdf.format(news.getPublishTime())+"',content='"+news.getContent()+"'  where id = "+news.getId());

              // 5、处理结果集

              System.out.println("修改了"+count+"条记录");

          } catch (Exception e) {

              e.printStackTrace();

          } finally {

              // 6、释放资源

              DBUtil.close(conn, st, rs);

          }

     }

     // 4、删除记录

     public void delete(int id) {

          try {

              conn = DBUtil.getConnection();

              // 3、创建Sql执行对象

              st = conn.createStatement();

              // 4、执行SQL

              int count = st.executeUpdate("delete from  t_news where id = "+id);

              // 5、处理结果集

              System.out.println("删除了"+count+"条记录");

          } catch (Exception e) {

              e.printStackTrace();

          } finally {

              // 6、释放资源

              DBUtil.close(conn, st, rs);

          }

     }

}

五、把数据库连接信息编写到 db.properties 文件中

    扩展名/后缀名：.txt , .doc , .jpg , .mp3

    .properties 叫做 属性文件，是java程序特有的一种文件。

    

    

    java.util.Properties类 ，可以读取属性文件中的内容。

    Properties类是Hashtable的子类，Hashtable实现了Map接口。

    Properties也是key-value键值对结构的，key和value固定了是String类型的。

    使用Properties类读取属性文件内容

/**

* 读取属性文件

* @author liwei

*

*/

public class PropertiesTest {

     public static void main(String[] args) throws  IOException {

          //1、创建Properties对象

          Properties props = new Properties();

          

          //2、加载属性文件的字节流

          props.load(PropertiesTest.class.getClassLoader().getResourceAsStream("db.properties"));

          

          //3、获取内容

          String url = props.getProperty("jdbc.url");

          String username =  props.getProperty("jdbc.username");

          String password =  props.getProperty("jdbc.password");

          String hello = props.getProperty("hello");

          

          System.out.println(url);

          System.out.println(username);

          System.out.println(password);

          System.out.println(hello);

     }

}

DBUtil工具类最终版

//数据库工具类

//封装重复性代码

public class DBUtil {

     private static String url;

     private static String username;

     private static String password;

     static {

          try {

              // 1、注册驱动

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

              // 读取db.properties文件

              Properties props = new Properties();

              props.load(DBUtil.class.getClassLoader().getResourceAsStream("db.properties"));

              url = props.getProperty("jdbc.url");

              username =  props.getProperty("jdbc.username");

              password =  props.getProperty("jdbc.password");

          } catch (Exception e) {

              e.printStackTrace();

          }

     }

     // 1、获取数据库连接

     public static Connection getConnection() {

          try {

              // 2、创建连接

              Connection conn =  DriverManager.getConnection(url, username, password);

              return conn;

          } catch (Exception e) {

              e.printStackTrace();

          }

          return null;

     }

     // 2、释放资源

     public static void close(Connection conn, Statement  st, ResultSet rs) {

          try {

              if (conn != null) {

                   conn.close();

              }

              if (st != null) {

                   st.close();

              }

              if (rs != null) {

                   rs.close();

              }

          } catch (SQLException e) {

              e.printStackTrace();

          }

     }

}

六、增删改查功能类的命名规范

    把增删改查的jdbc代码封装到一个类中，起名为 XxxDao 。

    dao : data 数据     access 访问     object 对象

             数据访问对象 -> 访问数据库。

    Dao类放在com.youzhong.dao包下

/**

* 新闻crud操作的类

* @author liwei

*

*/

public class NewsDao {

     private Connection conn = null;

     private Statement st = null;

     private ResultSet rs = null;

     // 1、查看列表

     public List<News> list() {

          List<News> list = new ArrayList<News>();

          try {

              conn = DBUtil.getConnection();

              // 3、创建Sql执行对象

              st = conn.createStatement();

              // 4、执行SQL

              rs = st.executeQuery("select * from  t_news");

              // 5、处理结果集

              while (rs.next()) {

                   int id = rs.getInt(1);

                   String title = rs.getString(2);

                   Date publishTime = rs.getTimestamp(3);

                   // 用SimpleDateFormat对java.util.Date进行格式化

                   // SimpleDateFormat sdf = new  SimpleDateFormat("yyyy/MM/dd HH:mm:ss");

                   String content = rs.getString(4);

                   // System.out.println(id + "\t" +  title + "\t" + sdf.format(publishTime) + "\t"

                   // + content);

                   // 把数据封装成News对象

                   News news = new News(id, title,  publishTime, content);

                   // 再把news对象添加到list集合中

                   list.add(news);

              }

          } catch (Exception e) {

              e.printStackTrace();

          } finally {

              // 6、释放资源

              DBUtil.close(conn, st, rs);

          }

          return list;

     }

     // 2、添加记录

     public void insert(News news) {

          try {

              conn = DBUtil.getConnection();

              // 3、创建Sql执行对象

              st = conn.createStatement();

              // 4、执行SQL

              SimpleDateFormat sdf = new  SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

              String sql = "insert into t_news values  (null,'" + news.getTitle() + "','"

                        +  sdf.format(news.getPublishTime()) + "','" +  news.getContent() + "')";

              System.out.println(sql);

              int count = st.executeUpdate(sql);

              // 5、处理结果集

              System.out.println("添加了" + count + "条记录");

          } catch (Exception e) {

              e.printStackTrace();

          } finally {

              // 6、释放资源

              DBUtil.close(conn, st, rs);

          }

     }

     // 3、修改记录

     public void update(News news) {

          try {

              conn = DBUtil.getConnection();

              // 3、创建Sql执行对象

              st = conn.createStatement();

              // 4、执行SQL

              SimpleDateFormat sdf = new  SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

              int count = st.executeUpdate("update t_news  set title='" + news.getTitle() + "',publish_time='"

              +  sdf.format(news.getPublishTime()) + "',content='" +  news.getContent() + "' where id = "+         news.getId());

              // 5、处理结果集

              System.out.println("修改了" + count + "条记录");

          } catch (Exception e) {

              e.printStackTrace();

          } finally {

              // 6、释放资源

              DBUtil.close(conn, st, rs);

          }

     }

     // 4、删除记录

     public void delete(int id) {

          try {

              conn = DBUtil.getConnection();

              // 3、创建Sql执行对象

              st = conn.createStatement();

              // 4、执行SQL

              int count = st.executeUpdate("delete from  t_news where id = " + id);

              // 5、处理结果集

              System.out.println("删除了" + count + "条记录");

          } catch (Exception e) {

              e.printStackTrace();

          } finally {

              // 6、释放资源

              DBUtil.close(conn, st, rs);

          }

     }

}

06-优化后jdbc开发

项目结构及起名如下图：

StudentDao如下图：

package com.youzhong.dao;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.Statement;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

import com.youzhong.entity.Student;

import com.youzhong.util.DBUtil;

public class StudentDao {

    // 三个资源

    private Connection conn = null;

    private Statement st = null;

    private ResultSet rs = null;

    private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

    // 1、列表

    public List<Student> list() {

        List<Student> list = new ArrayList<Student>();

        try {

            conn = DBUtil.getConnection();

            st = conn.createStatement();

            rs = st.executeQuery("select * from t_student");

            while (rs.next()) {

                int id = rs.getInt(1);

                String name = rs.getString(2);

                String sex = rs.getString(3);

                String phone = rs.getString(4);

                Date birthday = rs.getDate(5);

                Student stu = new Student(id, name, sex, phone, birthday);

                list.add(stu);

            }

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            DBUtil.close(conn, st, rs);

        }

        return list;

    }

    // 2、添加

    public void insert(Student student) {

        try {

            conn = DBUtil.getConnection();

            st = conn.createStatement();

            st.executeUpdate("insert into t_student values (null,'" + student.getName() + "','" + student.getSex()

                    + "','" + student.getPhone() + "','" + sdf.format(student.getBirthday()) + "')");

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            DBUtil.close(conn, st, rs);

        }

    }

    // 3、修改

    public void update(Student student) {

        try {

            conn = DBUtil.getConnection();

            st = conn.createStatement();

            st.executeUpdate("update t_student set name='" + student.getName() + "',sex='" + student.getSex()

                    + "',birthday='" + sdf.format(student.getBirthday()) + "' where id = " + student.getId());

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            DBUtil.close(conn, st, rs);

        }

    }

    // 4、删除

    public void delete(int id) {

        try {

            conn = DBUtil.getConnection();

            st = conn.createStatement();

            st.executeUpdate("delete from t_student where id = " + id);

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            DBUtil.close(conn, st, rs);

        }

    }

    // 5、模糊

    public List<Student> nameLike(String mohu) {

        List<Student> list = new ArrayList<Student>();

        try {

            conn = DBUtil.getConnection();

            st = conn.createStatement();

            rs = st.executeQuery("select * from t_student where name like '%" + mohu + "%'");

            while (rs.next()) {

                int id = rs.getInt(1);

                String name = rs.getString(2);

                String sex = rs.getString(3);

                String phone = rs.getString(4);

                Date birthday = rs.getDate(5);

                Student stu = new Student(id, name, sex, phone, birthday);

                list.add(stu);

            }

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            DBUtil.close(conn, st, rs);

        }

        return list;

    }

    // 6、批量删除

    public void deletes(int[] ids) {

        for (int id : ids) {

            delete(id);

        }

    }

    

    // 7、分页查询

    public List<Student> fenye(int start,int size) {

        List<Student> list = new ArrayList<Student>();

        try {

            conn = DBUtil.getConnection();

            st = conn.createStatement();

            rs = st.executeQuery("select * from t_student limit "+start+","+size);

            while (rs.next()) {

                int id = rs.getInt(1);

                String name = rs.getString(2);

                String sex = rs.getString(3);

                String phone = rs.getString(4);

                Date birthday = rs.getDate(5);

                Student stu = new Student(id, name, sex, phone, birthday);

                list.add(stu);

            }

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            DBUtil.close(conn, st, rs);

        }

        return list;

    }

    // 8、查总数

    public int getTotal() {

        try {

            conn = DBUtil.getConnection();

            st = conn.createStatement();

            rs = st.executeQuery("select count(*) from t_student");

            rs.next();

            return rs.getInt(1);

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            DBUtil.close(conn, st, rs);

        }

        return 0;

    }

    // 9、根据id查询

    public Student getById(int id) {

        try {

            conn = DBUtil.getConnection();

            st = conn.createStatement();

            rs = st.executeQuery("select * from t_student where id = "+id);

            if (rs.next()) {

                String name = rs.getString(2);

                String sex = rs.getString(3);

                String phone = rs.getString(4);

                Date birthday = rs.getDate(5);

                Student stu = new Student(id, name, sex, phone, birthday);

                return stu;

            }

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            DBUtil.close(conn, st, rs);

        }

        return null;

    }

}

07-jdbc开发注册登录功能 

注册登录功能是最常见，最基本的功能。

注册的本质：添加功能。

登录的本质：查询功能。

一、注册功能：

    添加帐号，密码。

// 1、注册

     public void zhuce(User user) {

          try {

              conn = DBUtil.getConnection();

              st = conn.createStatement();

              //先查询要注册的帐号是否存在

              String sql1 = "select * from t_user where  username = '"+user.getUsername()+"'";

              rs = st.executeQuery(sql1);

              if(rs.next()) {

                   //如果有记录，说明帐号已存在

                   System.out.println("帐号已存在");

                   return;

              }

              

              //不存在再注册

              String sql2 = "insert into t_user values  (null,'" + user.getUsername() + "','" +  user.getPassword() + "')";

              int count = st.executeUpdate(sql2);

              if (count > 0) {

                   System.out.println("注册成功");

              }

          } catch (Exception e) {

              e.printStackTrace();

          } finally {

              DBUtil.close(conn, st, rs);

          }

     }

二、登录功能

    根据帐号和密码进行查询。

// 2、登录

     public User denglu(User user) {

          try {

              conn = DBUtil.getConnection();

              st = conn.createStatement();

              String sql = "select * from t_user where  username = '"+user.getUsername()+"' and password =  '"+user.getPassword()+"'";

              rs = st.executeQuery(sql);

              //登录查询最多有一条记录

              if(rs.next()) {

                   //查到记录，登录成功

                   int id = rs.getInt(1);

                   String username = rs.getString(2);

                   String password = rs.getString(3);

                   return new User(id,username,password);

              }

          } catch (Exception e) {

              e.printStackTrace();

          }finally {

              DBUtil.close(conn, st, rs);

          }

          return null;

     }

三、上面的登录功能有bug

    万能帐号可以直接登录：' or 2=2  or ''='

    因为万能帐号对登录的sql进行了注入 ->  sql注入。

    sql注入就是传入了一个sql片断，被拼接到了sql字符串中，改变了sql原来的意思。

    

    使用 PreparedStatement 代替 Statement 解决sql注入问题。

    PreparedStatement 也是JDBC的一个API接口。

08-PreparedStatement使用 

一、jdbc的API

    位于java.sql包下

    DriverManager 类     -   驱动管理器

    Connection 接口        -    数据库连接

    Statement 接口         -     SQL执行对象（货车）  

    ResultSet 接口            -   结果集

    PreparedStatement 接口    -    预编译SQL执行对象（防暴汽车）

二、PreparedStatement简介 

    PreparedStatement是Statement的子接口。

    使用PreparedStatement解决登录功能的SQL注入问题：

// 2、登录

     public User denglu(User user) {

          try {

              conn = DBUtil.getConnection();

              // 使用Statement执行的SQL要用字符串拼接，使用PreparedStatement执行的SQL不需要用字符串拼接，参数用?占位

              String sql = "select * from t_user where  username = ? and password = ?";

              // 创建PreparedStatement

              ps = conn.prepareStatement(sql);

              

              //给?占位符赋值

              //第一个参数是?所占的位置

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

              ps.setString(2, user.getPassword());

              

              rs = ps.executeQuery();

              

              // 登录查询最多有一条记录

              if (rs.next()) {

                   // 查到记录，登录成功

                   int id = rs.getInt(1);

                   String username = rs.getString(2);

                   String password = rs.getString(3);

                   return new User(id, username,  password);

              }

          } catch (Exception e) {

              e.printStackTrace();

          } finally {

              DBUtil.close(conn, st, rs);

          }

          return null;

     }

三、PreparedStatement开发步骤：

    1、先写出SQL，参数使用?占位符（?两边不需要加单引号）

String sql = "select * from t_user where username = ? and  password = ?";

    2、使用conn连接对象创建PreparedStatement

         把sql传入，进行预编译（预检查）

PreparedStatement ps = conn.prepareStatement(sql);

    3、给ps对象中的sql占位符赋值

         第1个?号所占的位置是1，第2个问号所占的位置是2.

         赋值的时候，set方法使用的?占位符必须与实际?占位符匹配。

ps.setString(1,"xxx");

ps.setString(2,"yyy");

ps.setInt(?号位置,整数);

ps.setDouble(?号位置,小数);

//日期类型

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

ps.setString(?号位置,sdf.format(new Date()));

    4、使用ps执行SQL

ResultSet rs = ps.executeQuery(); //执行查询

int count = ps.executeUpdate(); //执行增删改

球员项目

package com.youzhong.dao;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

import com.youzhong.entity.Player;

import com.youzhong.util.DBUtil;

public class PlayerDao {

    private Connection conn = null;

    private PreparedStatement ps = null;

    private ResultSet rs = null;

    private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

    // 1、列表

    public List<Player> list() {

        List<Player> list = new ArrayList<Player>();

        try {

            conn = DBUtil.getConnection();

            String sql = "select * from t_player";

            ps = conn.prepareStatement(sql);

            rs = ps.executeQuery();

            while(rs.next()) {

                int id = rs.getInt(1);

                String name = rs.getString(2);

                String teamName = rs.getString(3);

                int num = rs.getInt(4);

                String position = rs.getString(5);

                Date birthday = rs.getDate(6);

                Player player = new Player(id,name,teamName,num,position,birthday);

                list.add(player);

            }

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            DBUtil.close(conn, ps, rs);

        }

        return list;

    }

    // 2、添加

    public void insert(Player player) {

        try {

            conn = DBUtil.getConnection();

            String sql = "insert into t_player values (null,?,?,?,?,?)";

            ps = conn.prepareStatement(sql);

            ps.setString(1, player.getName());

            ps.setString(2, player.getTeamName());

            ps.setInt(3, player.getNum());

            ps.setString(4, player.getPosition());

            ps.setString(5, sdf.format(player.getBirthday()));

            ps.executeUpdate();

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            DBUtil.close(conn, ps, rs);

        }

    }

    // 3、修改

    public void update(Player player) {

        try {

            conn = DBUtil.getConnection();

            String sql = "update t_player set name=?,team_name=?,num=?,position=?,birthday=? where id=?";

            ps = conn.prepareStatement(sql);

            ps.setString(1, player.getName());

            ps.setString(2, player.getTeamName());

            ps.setInt(3, player.getNum());

            ps.setString(4, player.getPosition());

            ps.setString(5, sdf.format(player.getBirthday()));

            ps.setInt(6, player.getId());

            ps.executeUpdate();

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            DBUtil.close(conn, ps, rs);

        }

    }

    // 4、删除

    public void delete(int id) {

        try {

            conn = DBUtil.getConnection();

            String sql = "delete from t_player where id=?";

            ps = conn.prepareStatement(sql);

            ps.setInt(1, id);

            ps.executeUpdate();

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            DBUtil.close(conn, ps, rs);

        }

    }

    // 5、模糊

    public List<Player> nameLike(String mohu) {

        List<Player> list = new ArrayList<Player>();

        try {

            conn = DBUtil.getConnection();

            String sql = "select * from t_player where name like ?";

            ps = conn.prepareStatement(sql);

            ps.setString(1, "%"+mohu+"%");

            rs = ps.executeQuery();

            while(rs.next()) {

                int id = rs.getInt(1);

                String name = rs.getString(2);

                String teamName = rs.getString(3);

                int num = rs.getInt(4);

                String position = rs.getString(5);

                Date birthday = rs.getDate(6);

                Player player = new Player(id,name,teamName,num,position,birthday);

                list.add(player);

            }

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            DBUtil.close(conn, ps, rs);

        }

        return list;

    }

    // 6、批量

    public void deletes(int[] ids) {

        for (int id : ids) {

            delete(id);

        }

    }

    // 7、分页

    public List<Player> fenye(int start,int size) {

        List<Player> list = new ArrayList<Player>();

        try {

            conn = DBUtil.getConnection();

            String sql = "select * from t_player limit ?,?";

            ps = conn.prepareStatement(sql);

            ps.setInt(1, start);

            ps.setInt(2, size);

            rs = ps.executeQuery();

            while(rs.next()) {

                int id = rs.getInt(1);

                String name = rs.getString(2);

                String teamName = rs.getString(3);

                int num = rs.getInt(4);

                String position = rs.getString(5);

                Date birthday = rs.getDate(6);

                Player player = new Player(id,name,teamName,num,position,birthday);

                list.add(player);

            }

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            DBUtil.close(conn, ps, rs);

        }

        return list;

    }

    // 8、查总数

    public int getTotal() {

        try {

            conn = DBUtil.getConnection();

            String sql = "select count(*) from t_player";

            ps = conn.prepareStatement(sql);

            rs = ps.executeQuery();

            rs.next();

            return rs.getInt(1);

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            DBUtil.close(conn, ps, rs);

        }

        return 0;

    }

    // 9、根据id查询

    public Player getById(int id) {

        try {

            conn = DBUtil.getConnection();

            String sql = "select * from t_player where id = ?";

            ps = conn.prepareStatement(sql);

            ps.setInt(1, id);

            rs = ps.executeQuery();

            if(rs.next()) {

                String name = rs.getString(2);

                String teamName = rs.getString(3);

                int num = rs.getInt(4);

                String position = rs.getString(5);

                Date birthday = rs.getDate(6);

                Player player = new Player(id,name,teamName,num,position,birthday);

                return player;

            }

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            DBUtil.close(conn, ps, rs);

        }

        return null;

    }

}

四、PreparedStatement与Statement的区别？

    1、PreparedStatement安全，没有sql注入的风险。Statement不安全，会被SQL注入

    2、PreparedStatement执行的sql是?号占位，不用字符串拼接，更简单。Statement执行的SQL用字符串拼接，容易出错。

    3、PreparedStatement会对SQL进行预编译，速度更快。Statement速度慢。

09-事务 

事务是数据库中一个非常重要的概念。

一、什么是事务

    事务指逻辑上的一组操作，要么全成功，要么全失败，不能有中间状态。

    比如转帐操作：A -> B 转100元

        sql1，A-100元:

update t_card set money = money - 100 where username = 'A';

        sql2，B+100元：

update t_card set money = money + 100 where username = 'B';

二、事务的四大特性：

    1、原子性：这一组操作是一个整体，就像一个原子一样，不可分隔。

    2、一致性：事务提交前和提交后，数据库的数据状态一致。

            A->B转100元    B->A转300元

            A    1000        900                 1200

            B     2000        2100               1800

    3、隔离性：当事务并发执行时，要把每个事务合理的隔离开，不能产生影响

            

                                老妈存钱1000元    1000+1000 = 2000    

    A 帐户1000元

                                 商场消费500元     1000-500 = 500

    把2000存入数据库不对，把500存入数据库也不对。

    应该先执行一件，再执行一件，先存1000，结果是2000，存入数据库。再消费500，用2000-500，结果剩下1500。

        再比如，我们要办两件事，吃饭，上厕所，需要隔离开。

    4、持久性：当事务提交后，数据就永远保存下来。不会因为断电或电脑进水等情况而丢失。（数据存到硬盘上）

三、mysql控制事务的语句（TCL）

     start transaction   开启事务

     commit                 提交事务

     rollback                 回滚事务

START TRANSACTION;

update t_card set money = money - 100 where username = 'A';

asdfasdfasdaasdfs

update t_card set money = money + 100 where username = 'B';

COMMIT;

四、使用JDBC控制事务

    conn.setAutoCommit(false);   开启事务 （关闭自动提交）

    conn.commit();                       提交事务

    conn.rollback();                      回滚事务

// 2、添加

     public void insert(Player player) {

          try {

              conn = DBUtil.getConnection();

              //开启事务

              conn.setAutoCommit(false);

              String sql = "insert into t_player values  (null,?,?,?,?,?)";

              ps = conn.prepareStatement(sql);

              ps.setString(1, player.getName());

              ps.setString(2, player.getTeamName());

              ps.setInt(3, player.getNum());

              ps.setString(4, player.getPosition());

              ps.setString(5,  sdf.format(player.getBirthday()));

              ps.executeUpdate();

              //提交事务

              //conn.commit();

              

              //回滚事务

              conn.rollback();

          } catch (Exception e) {

              e.printStackTrace();

          } finally {

              DBUtil.close(conn, ps, rs);

          }

     }

    使用JDBC事务保证转帐的安全性

public class CardDao {

     private Connection conn = null;

     private PreparedStatement ps = null;

     private ResultSet rs = null;

     private SimpleDateFormat sdf = new  SimpleDateFormat("yyyy-MM-dd");

     // 转帐

     public void zhuanzhang(String username1, String  username2, double money) {

          try {

              conn = DBUtil.getConnection();

              //开事务

              conn.setAutoCommit(false);

              

              String sql1 = "update t_card set money =  money - ? where username = ?";

              ps = conn.prepareStatement(sql1);

              ps.setDouble(1, money);

              ps.setString(2, username1);

              ps.executeUpdate();

              

              //int i = 1/0;//抛异常

              

              String sql2 = "update t_card set money =  money + ? where username = ?";

              ps = conn.prepareStatement(sql2);

              ps.setDouble(1, money);

              ps.setString(2, username2);

              ps.executeUpdate();

              

              //提交事务

              conn.commit();

          } catch (Exception e) {

              //回滚事务

              try {

                   conn.rollback();

              } catch (SQLException e1) {

                   e1.printStackTrace();

              }

              e.printStackTrace();

          } finally {

              DBUtil.close(conn, ps, rs);

          }

     }

}

五、事务的四个隔离级别

    1、读未提交 

    2、读已提交

    3、可重复读

    4、序列化

    上面的四个隔离级别1，2，3，4是由低到高的。

    最低的级别是1读未提交，最高的级别是4序列化。

    MySQL默认隔离级别是 3 可重复读。

    隔离级别越低，两个事务隔离性越差，互相的影响最大，数据越不安全，效率最高。

    隔离级别越高，两个事务隔离性越好，互相的影响最小，数据越安全，效率越低。

    

六、事务不同隔离级别导致的不同问题

1

读未提交 

脏读，不可重复读，幻读

2

读已提交 

不可重复读，幻读

3

可重复读

幻读

4

序列化


http://www.kler.cn/a/381094.html

相关文章：

  • IT运维的365天--021 服务器上的dns设置后不起作用
  • Android Https和WebView
  • 记录仪方案_记录仪安卓主板定制_音视频记录仪PCBA定制开发
  • 机器学习基础算法 (二)-逻辑回归
  • malloc 分配大堆块（128KB）的一次探索
  • 如何优雅的关闭GoWeb服务器
  • MySQL初学之旅（1）配置与基础操作
  • 大数据-205 数据挖掘 机器学习理论 - 线性回归 最小二乘法 多元线性
  • Vue3版本的uniapp项目运行至鸿蒙系统
  • 数据结构(8.7_3)置换——选择排序
  • 【P2-8】ESP8266 WIFI模块在STA+AP模式下相关指令及注意事项
  • RDD转换算子：重分区算子：【repartition、coalesce】
  • [C++ 核心编程]笔记 4.2.5 深拷贝与浅拷贝
  • Hive学习笔记
  • SQL入门的基础知识
  • SQL 像英语是个善意的错误
  • openapi回调地址请求不通过
  • 医院信息化与智能化系统(17)
  • iOS 再谈KVC、 KVO
  • 【Web自动化】探索Selenium与WebDriver的核心原理
  • Python OpenCV 图像改变
  • AI大模型赋能医学诊疗与药学服务——课题基金申请辅导项目成功举办
  • 安装Blender并使用
  • 【C++之STL】一文学会使用 string
  • 代码随想录之哈希表刷题总结
  • Redis学习：1. Redlock算法(MultiLock)和底层源码分析、2. Redis缓存过期淘汰策略