Eclipse中java向数据库中添加数据，更新数据，删除数据

2022-01-19 08:29:57

前面详细写过如何连接数据库的具体操作，下面介绍向数据库中添加数据。

注意事项：如果参考下面代码，需要

改包名，数据库名，数据库账号，密码，和数据表（数据表里面的信息）

 1 package com.ningmeng;
 2 
 3 import java.sql.*;
 4 
 5 /**
 6  * 1:向数据库中添加数据
 7  * @author biexiansheng
 8  *
 9  */
10 public class Test01 {
11 
12     public static void main(String[] args) {
13         try {
14             Class.forName("com.mysql.jdbc.Driver");//加载数据库驱动
15             System.out.println("加载数据库驱动成功");
16             String url="jdbc:mysql://localhost:3306/test";//声明数据库test的url
17             String user="root";//数据库的用户名
18             String password="123456";//数据库的密码
19             //建立数据库连接，获得连接对象conn(抛出异常即可)
20             Connection conn=DriverManager.getConnection(url, user, password);
21             System.out.println("连接数据库成功");
22             //生成一条mysql语句
23             String sql="insert into users(username,password,age,sex) values('小别','123456',22,0)";        
24             Statement stmt=conn.createStatement();//创建一个Statement对象
25             stmt.executeUpdate(sql);//执行sql语句
26             System.out.println("插入到数据库成功");
27             conn.close();
28             System.out.println("关闭数据库成功");
29         } catch (ClassNotFoundException e) {
30             // TODO Auto-generated catch block
31             e.printStackTrace();
32         }//
33         catch (SQLException e) {
34             // TODO Auto-generated catch block
35             e.printStackTrace();
36         }
37         
38     }
39     
40 }

详细运行结果

这样就可以完美插入数据，增删改查第一步完美解决。

简单介绍一下所使用的知识点：

在java程序中一旦建立了数据库的连接，就可以使用Connection接口的createStatement()方法来获得statement对象

然后通过excuteUpdate()方法来执行sql语句，就可以向数据库中添加数据了。

1：createStatement()方法是Connection接口的方法，用来创建Statement对象

2：Connection接口代表和特定的数据库连接，要对数据库中数据表的数据进行操作，首先要获取数据库连接。

3：Statement接口用于创建向数据库中传递SQL语句的对象，该接口提供了一些方法可以实现对数据库的常用操作。

4：Statement接口中的excuteUpdate()方法执行给定的SQL语句，该语句可以是INSERT,UPDATE,DELETE语句。

第二种方法

使用PreparedStatement接口向mysql数据库中插入数据

 1 package com.ningmeng;
 2 
 3 import java.sql.*;
 4 
 5 /**
 6  * 1:使用PreparedStatement接口来执行插入语句
 7  * 
 8  * @author biexiansheng
 9  *
10  */
11 public class Test02 {
12 
13     public static void main(String[] args) {
14         // TODO Auto-generated method stub
15         try {
16             Class.forName("com.mysql.jdbc.Driver");//加载数据库驱动
17             System.out.println("加载数据库驱动成功");
18             String url="jdbc:mysql://localhost:3306/test";//声明数据库test的url
19             String user="root";//数据库用户名
20             String password="123456";//数据库密码
21             //建立数据库连接，获得连接对象conn
22             Connection conn=DriverManager.getConnection(url, user, password);
23             System.out.println("连接数据库驱动成功");
24             //生成一条SQL语句
25             String sql="insert into users(username,password,age,sex) values(?,?,?,?)";
26             PreparedStatement ps=conn.prepareStatement(sql);//创建一个Statement对象
27             ps.setNString(1,"lisi");//为sql语句中第一个问号赋值
28             ps.setString(2,"123456");//为sql语句中第二个问号赋值
29             ps.setInt(3,24);//为sql语句第三个问号赋值
30             ps.setInt(4,2);//为sql语句的第四个问号赋值
31             ps.executeUpdate();//执行sql语句
32             conn.close();//关闭数据库连接对象
33             System.out.println("关闭数据库连接对象");
34         } catch (ClassNotFoundException e) {
35             // TODO Auto-generated catch block
36             e.printStackTrace();
37         } catch (SQLException e) {
38             // TODO Auto-generated catch block
39             e.printStackTrace();
40         }
41         
42         
43     }
44 
45 }

由于刚才不小心多执行了一遍第一个程序，所以多了一行id==7的，特此注释一下

1：PreparedStatement接口继承Statement，用于执行动态的SQL语句，通过PreparedStatement实例执行SQL语句，将被预编译并且保存到PreparedStatement实例中，从而可以反复的执行该SQL语句。

2：PreparementStatement接口中的方法，如executeUpdate在此PrepareStatement对象中执行sql语句，该sql语句必须是一个INSERT.UPDATE,DELETE语句，或者是没有返回值的DDL语句。

3：setString(int pIndex,String str)将参数pIndex位置上设置为给定的String类型的参数，俗语就是在第几个位置写上符合的数据类型

setInt(int pIndex,int x)

其他的都类似，不作多叙述

更深层次的理解JDBC对java程序和数据库之间的操作

JDBC技术的常用类和接口

必须搞明白这些关系，不能只会写代码，理解其含义。
（1）：必须清楚，JDBC是一种可用于执行SQL语句的JAVA API(Application Programming Interface,应用程序设计接口)，是连接数据库和java应用程序的一个纽带。

（2）：DriverManager类用来管理数据库中的所有驱动程序，是JDBC的管理层，作用与用户和驱动程序之间，跟踪可用的驱动程序，并在数据库的驱动程序之间建立连接。
DriverManager类最常用的方法是
getConnection(String url,String user,String password);

（3）：Connection接口代表与特定的数据库的连接，要对数据表中的数据进行操作，首先要获取数据库的连接。Connection实例就像在应用程序与数据库之间开通了一条通道。
可通过DriverManager类的getConnection()方法获取Connection实例。
比如：
Connection conn=DriverManager.getConnection(url, user, password);
Statement stmt=conn.createStatement();//创建一个Statement对象

Connection接口常用的方法是：
createStatement()创建Statement对象
close()立即释放此Connection对象的数据库和JDBC资源，而不是等待它们被自动释放。

（4）：Statement接口用于创建向数据库中传递SQL语句的对象，该接口提供了一些方法可以实现对数据库的常用操作。（Statement接口用于执行静态SQL语句，并返回它所生成结果的对象）
Statement接口常用的方法
execute(String sql);执行静态的SELECT语句，该语句可能返回多个结果集
executeQuery(String sql);执行给定的SQL语句，该语句返回单个ResultSet对象。
executeUpdate()执行给定的SQL语句，该语句可以为INSERT,UPDATE,DELETE语句。
close()释放Statement实例占用的数据库和JDBC资源。

（5）：PreparedStatement接口继承了Statement接口，用于执行动态的SQL语句，通过PreparedStatement实例执行的sql语句，将被预编译并保存到PreparedStatement实例中，从而可以反复的执行该SQL语句。
PreparedStatement接口的常用方法。
execute();在此PreparedStatement对象中执行SQL语句，该语句可以是任何类型的SQL语句。
executeQuery()在此PreparedStatement对象中执行SQL查询语句，返回结果为查询结果集ResultSet对象。
executeUpdate()在此PreparedStatement对象中执行SQL语句，该语句必须是一个INSERT,UPDATE,DELETE语句。或者是没有返回值的DDL语句。
close()释放Statement实例占用的数据库和JDBC资源。

（6）：ResultSet接口类似与一个临时表，用来暂时存放数据库查询操作所获得的结果集，

下面写几个程序更深层次的理解一下JDBC作为连接数据库的JAVA程序纽带
1：首先封装了通用的一些东西，然后通过引入调用（需要注意的是包名，类名，mysql数据库账号，密码，数据库名，数据表名，字段等等。）

 1 package com.util;
 2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.SQLException;
 6 
 7 public class Dbutil {
 8     
 9     private static String url="jdbc:mysql://localhost:3306/db_person";//生命数据库的url(地址)
10     private static String user="root";//数据库账号
11     private static String password="123456";//数据库密码
12     private static String driver="com.mysql.jdbc.Driver";//数据库的驱动
13     
14     /**
15      * 
16      * @return
17      * @throws Exception
18      */
19     public Connection getCon() throws Exception{
20         Class.forName(driver);//加载数据库驱动
21         Connection con=DriverManager.getConnection(url, user, password);
22         //建立数据库的连接，获得连接对象con
23         return con;
24     }
25     
26     /**
27      * 
28      * @param con
29      * @throws Exception
30      */
31     public void close(Connection con) throws Exception{
32         if(con!=null){
33             con.close();
34         }    
35     }
36     
37     
38 }

封装的通用的一些东西

 1 package com.ningmeng;
 2 
 3 import java.sql.Connection;
 4 import java.sql.Statement;
 5 
 6 import com.util.Dbutil;
 7 
 8 public class Test {
 9   
10      public static void main(String args[]) throws Exception {  
11          Dbutil db=new Dbutil();
12          String sql="insert into db_book values(null,'javaweb',888,'小别',1)";//生成一条sql语句
13          Connection con=db.getCon();//获取数据库的连接
14          Statement stmt=con.createStatement();//创建一个Statement连接
15          int result=stmt.executeUpdate(sql);//执行sql语句
16          System.out.println("执行了"+result+"数据");
17          stmt.close();//关闭顺序是先关闭小的，后关闭大的
18          con.close();
19          
20      }     
21 }

核心代码

2：数据库的插入一般都是从前台获取的，上面这个例子不是很好，下面举例另一种方式。

（插入数据时使用拼接）同样引用上面封装的通用的一些东西！！！

 1 package com.ningmeng;
 2 
 3 import java.sql.Connection;
 4 import java.sql.Statement;
 5 
 6 import com.util.Dbutil;
 7 
 8 public class Test2 {
 9 
10     private static Dbutil db=new Dbutil();
11     private static int add(String name,float price,String author,int bookTypeId)
12         throws Exception{
13         Connection con=db.getCon();
14         String sql="insert into db_book values(null,'"+name+"',"+price+",'"+author+"',"+bookTypeId+")";
15         Statement stmt=con.createStatement();//创建一个Statement连接
16         int result=stmt.executeUpdate(sql);//执行sql语句
17         db.close(stmt,con);
18         return result;
19     }
20     public static void main(String[] args) throws Exception {
21         // TODO Auto-generated method stub
22         int result=add("java",888,"小卡",1);
23         if(result==1){
24             System.out.println("添加成功");
25         }else{
26             System.out.println("添加失败");
27         }
28         
29     }
30 
31 }

核心代码

（可以发现已经添加成功了）

3：下面使用面向对象的思想传入数据（实现的时候和上面的一同完成操作，）

 1 package com.ningmeng;
 2 
 3 public class Book {
 4     
 5     private String name;
 6     private float price;
 7     private String author;
 8     private int bookTypeId;
 9     public String getName() {
10         return name;
11     }
12     public void setName(String name) {
13         this.name = name;
14     }
15     public float getPrice() {
16         return price;
17     }
18     public void setPrice(float price) {
19         this.price = price;
20     }
21     public String getAuthor() {
22         return author;
23     }
24     public void setAuthor(String author) {
25         this.author = author;
26     }
27     public int getBookTypeId() {
28         return bookTypeId;
29     }
30     public void setBookTypeId(int bookTypeId) {
31         this.bookTypeId = bookTypeId;
32     }
33     public Book(String name, float price, String author, int bookTypeId) {
34         super();
35         this.name = name;
36         this.price = price;
37         this.author = author;
38         this.bookTypeId = bookTypeId;
39     }
40     
41     
42     
43     
44 }

封装，面向对象的思想

 1 package com.ningmeng;
 2 
 3 import java.sql.Connection;
 4 import java.sql.Statement;
 5 
 6 import com.util.Dbutil;
 7 
 8 public class Test2 {
 9 
10     private static Dbutil db=new Dbutil();
11     
12     private static int add2(Book book) throws Exception{
13         Connection con=db.getCon();
14         String sql="insert into db_book values(null,'"+book.getName()+"',"+book.getPrice()+",'"+book.getAuthor()+"',"+book.getBookTypeId()+")";
15         Statement stmt=con.createStatement();//创建一个Statement连接
16         int result=stmt.executeUpdate(sql);//执行sql语句
17         db.close(stmt,con);
18         return result;
19     }
20     
21     private static int add(String name,float price,String author,int bookTypeId)
22         throws Exception{
23         Connection con=db.getCon();
24         String sql="insert into db_book values(null,'"+name+"',"+price+",'"+author+"',"+bookTypeId+")";
25         Statement stmt=con.createStatement();//创建一个Statement连接
26         int result=stmt.executeUpdate(sql);//执行sql语句
27         db.close(stmt,con);
28         return result;
29     }
30     public static void main(String[] args) throws Exception {
31         // TODO Auto-generated method stub
32         /*int result=add("java",888,"小卡",1);
33         if(result==1){
34             System.out.println("添加成功");
35         }else{
36             System.out.println("添加失败");
37         }*/
38         
39         Book book=new Book("java2",888,"小卡2",1);
40         int result=add2(book);
41         if(result==1){
42             System.out.println("添加成功");
43         }else{
44             System.out.println("添加失败");
45         }    
46     
47     }
48 }

核心代码

 1 package com.util;
 2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.SQLException;
 6 import java.sql.Statement;
 7 
 8 public class Dbutil {
 9     
10     private static String url="jdbc:mysql://localhost:3306/db_person";//生命数据库的url(地址)
11     private static String user="root";//数据库账号
12     private static String password="123456";//数据库密码
13     private static String driver="com.mysql.jdbc.Driver";//数据库的驱动
14     
15     /**
16      * 
17      * @return
18      * @throws Exception
19      */
20     public Connection getCon() throws Exception{
21         Class.forName(driver);//加载数据库驱动
22         Connection con=DriverManager.getConnection(url, user, password);
23         //建立数据库的连接，获得连接对象con
24         return con;
25     }
26     
27     /**
28      * 
29      * @param con
30      * @throws Exception
31      */
32     public void close(Statement stmt,Connection con) throws Exception{
33         if(stmt!=null){
34             stmt.close();
35             if(con!=null){
36                 con.close();
37             }
38         }
39             
40     }
41     
42     
43 }

通用的

1：使用Statement更新操作

 1 package com.ningmeng;
 2 
 3 import java.sql.Connection;
 4 import java.sql.Statement;
 5 
 6 import com.util.Dbutil;
 7 
 8 /**
 9  * 更新操作
10  * @author biexiansheng
11  *
12  */
13 public class Test3 {
14     
15     private static Dbutil db=new Dbutil();
16     
17     private static int update(Book book) throws Exception{
18         Connection con=db.getCon();
19         String sql="update db_book set name='"+book.getName()+"',price="+book.getPrice()+",author='"+book.getAuthor()+"',bookTypeId="+book.getBookTypeId()+" where id=13";
20         Statement stmt=con.createStatement();//创建一个Statement连接
21         int result=stmt.executeUpdate(sql);//执行sql语句
22         db.close(stmt,con);
23         return result;
24     }
25     
26     public static void main(String[] args) throws Exception {
27         Book book=new Book(13,"java120",666,"小别",1);
28         int result=update(book);
29         if(result==1){
30             System.out.println("更新成功");
31         }else{
32             System.out.println("更新失败");
33         }    
34     }
35 }

核心代码

 1 package com.util;
 2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.SQLException;
 6 import java.sql.Statement;
 7 
 8 public class Dbutil {
 9     
10     private static String url="jdbc:mysql://localhost:3306/db_person";//生命数据库的url(地址)
11     private static String user="root";//数据库账号
12     private static String password="123456";//数据库密码
13     private static String driver="com.mysql.jdbc.Driver";//数据库的驱动
14     
15     /**
16      * 
17      * @return
18      * @throws Exception
19      */
20     public Connection getCon() throws Exception{
21         Class.forName(driver);//加载数据库驱动
22         Connection con=DriverManager.getConnection(url, user, password);
23         //建立数据库的连接，获得连接对象con
24         return con;
25     }
26     
27     /**
28      * 
29      * @param con
30      * @throws Exception
31      */
32     public void close(Statement stmt,Connection con) throws Exception{
33         if(stmt!=null){
34             stmt.close();
35             if(con!=null){
36                 con.close();
37             }
38         }
39             
40     }
41     
42     
43 }

通用代码

 1 package com.ningmeng;
 2 
 3 public class Book {
 4     
 5     private int id;
 6     private String name;
 7     private float price;
 8     private String author;
 9     private int bookTypeId;
10     public String getName() {
11         return name;
12     }
13     public void setName(String name) {
14         this.name = name;
15     }
16     public float getPrice() {
17         return price;
18     }
19     public void setPrice(float price) {
20         this.price = price;
21     }
22     public String getAuthor() {
23         return author;
24     }
25     public void setAuthor(String author) {
26         this.author = author;
27     }
28     public int getBookTypeId() {
29         return bookTypeId;
30     }
31     public void setBookTypeId(int bookTypeId) {
32         this.bookTypeId = bookTypeId;
33     }
34     
35     public int getId() {
36         return id;
37     }
38     public void setId(int id) {
39         this.id = id;
40     }
41     
42     public Book(int id, String name, float price, String author, int bookTypeId) {
43         super();
44         this.id = id;
45         this.name = name;
46         this.price = price;
47         this.author = author;
48         this.bookTypeId = bookTypeId;
49     }
50     
51     
52     
53     
54     
55     
56 }

封装代码

（已经完成更新操作，需要注意的是在执行sql语句的时候由于语句过长可以使用eclipse自带的排版功能，完成排版）

1：使用Statement执行删除操作

 1 package com.ningmeng;
 2 
 3 import java.sql.Connection;
 4 import java.sql.Statement;
 5 
 6 import com.util.Dbutil;
 7 
 8 public class Test4 {
 9 
10     private static Dbutil db=new Dbutil();
11     
12     private static int delete(Book book) throws Exception{
13         Connection con=db.getCon();
14         String sql="delete from db_book where id="+book.getId();
15         Statement stmt=con.createStatement();//创建一个Statement连接
16         int result=stmt.executeUpdate(sql);//执行sql语句
17         db.close(stmt,con);
18         return result;
19     }
20     public static void main(String[] args) throws Exception{
21         // TODO Auto-generated method stub
22         Book book=new Book(13);
23         int result=delete(book);
24         if(result==1){
25             System.out.println("删除成功");
26         }else{
27             System.out.println("删除失败");
28         }    
29     }
30 
31 }

核心代码

 1 package com.util;
 2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.SQLException;
 6 import java.sql.Statement;
 7 
 8 public class Dbutil {
 9     
10     private static String url="jdbc:mysql://localhost:3306/db_person";//生命数据库的url(地址)
11     private static String user="root";//数据库账号
12     private static String password="123456";//数据库密码
13     private static String driver="com.mysql.jdbc.Driver";//数据库的驱动
14     
15     /**
16      * 
17      * @return
18      * @throws Exception
19      */
20     public Connection getCon() throws Exception{
21         Class.forName(driver);//加载数据库驱动
22         Connection con=DriverManager.getConnection(url, user, password);
23         //建立数据库的连接，获得连接对象con
24         return con;
25     }
26     
27     /**
28      * 
29      * @param con
30      * @throws Exception
31      */
32     public void close(Statement stmt,Connection con) throws Exception{
33         if(stmt!=null){
34             stmt.close();
35             if(con!=null){
36                 con.close();
37             }
38         }
39             
40     }
41     
42     
43 }

通用的

 1 package com.ningmeng;
 2 
 3 public class Book {
 4     
 5     private int id;
 6     private String name;
 7     private float price;
 8     private String author;
 9     private int bookTypeId;
10     public String getName() {
11         return name;
12     }
13     public void setName(String name) {
14         this.name = name;
15     }
16     public float getPrice() {
17         return price;
18     }
19     public void setPrice(float price) {
20         this.price = price;
21     }
22     public String getAuthor() {
23         return author;
24     }
25     public void setAuthor(String author) {
26         this.author = author;
27     }
28     public int getBookTypeId() {
29         return bookTypeId;
30     }
31     public void setBookTypeId(int bookTypeId) {
32         this.bookTypeId = bookTypeId;
33     }
34     
35     public int getId() {
36         return id;
37     }
38     public void setId(int id) {
39         this.id = id;
40     }
41     public Book(int id) {
42         super();
43         this.id = id;
44     }
45     
46     
47     
48     
49     
50 }

封装

（可以看到删除操作执行完毕。）

1：PreparedStatement是Statement的子接口，属于预处理操作，与直接使用Statement不同的是，PreparedStatement在操作时，是先在数据表中准备好了一条SQL语句，但是此SQL语句的具体内容暂时不设置，而是之后再进行设置。
（以后开发一般用PreparedStatement，一般不用Statement）

2:PreparedStatement插入操作

 1 package com.ningmeng;
 2 
 3 import java.sql.Connection;
 4 import java.sql.PreparedStatement;
 5 
 6 import com.util.Dbutil;
 7 
 8 public class Test5 {
 9     
10     private static Dbutil db=new Dbutil();
11     
12     private static int add(Book book) throws Exception{
13         Connection con=db.getCon();//建立数据库的连接
14         String sql="insert into db_book values(null,?,?,?,?)";//生成一条SQL语句
15         PreparedStatement pstmt=con.prepareStatement(sql);//创建Statement对象
16         pstmt.setString(1,book.getName());
17         pstmt.setFloat(2,book.getPrice());
18         pstmt.setString(3,book.getAuthor());
19         pstmt.setInt(4,book.getBookTypeId());
20         int result=pstmt.executeUpdate();//执行SQL语句
21         db.close(pstmt,con);
22         return result;
23     }
24     public static void main(String[] args) throws Exception{
25         // TODO Auto-generated method stub
26         Book book =new Book("openstack",999,"小别",1);
27         int result=add(book);
28         if(result==1){
29             System.out.println("插入成功");
30         }else{
31             System.out.println("插入失败");
32         }
33     }
34 
35 }

核心代码

 1 package com.ningmeng;
 2 
 3 public class Book {
 4     
 5     private int id;
 6     private String name;
 7     private float price;
 8     private String author;
 9     private int bookTypeId;
10     public String getName() {
11         return name;
12     }
13     public void setName(String name) {
14         this.name = name;
15     }
16     public float getPrice() {
17         return price;
18     }
19     public void setPrice(float price) {
20         this.price = price;
21     }
22     public String getAuthor() {
23         return author;
24     }
25     public void setAuthor(String author) {
26         this.author = author;
27     }
28     public int getBookTypeId() {
29         return bookTypeId;
30     }
31     public void setBookTypeId(int bookTypeId) {
32         this.bookTypeId = bookTypeId;
33     }
34     
35     public int getId() {
36         return id;
37     }
38     public void setId(int id) {
39         this.id = id;
40     }
41     public Book(String name, float price, String author, int bookTypeId) {
42         super();
43         this.name = name;
44         this.price = price;
45         this.author = author;
46         this.bookTypeId = bookTypeId;
47     }
48     
49     
50     
51     
52     
53     
54 }

封装

 1 package com.util;
 2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.PreparedStatement;
 6 import java.sql.Statement;
 7 
 8 public class Dbutil {
 9     
10     private static String url="jdbc:mysql://localhost:3306/db_person";//生命数据库的url(地址)
11     private static String user="root";//数据库账号
12     private static String password="123456";//数据库密码
13     private static String driver="com.mysql.jdbc.Driver";//数据库的驱动
14     
15     /**
16      * 
17      * @return
18      * @throws Exception
19      */
20     public Connection getCon() throws Exception{
21         Class.forName(driver);//加载数据库驱动
22         Connection con=DriverManager.getConnection(url, user, password);
23         //建立数据库的连接，获得连接对象con
24         return con;
25     }
26     
27     /**
28      * 
29      * @param con
30      * @throws Exception
31      */
32     public void close(Statement stmt,Connection con) throws Exception{
33         if(stmt!=null){
34             stmt.close();
35             if(con!=null){
36                 con.close();
37             }
38         }
39     }
40     
41     public void close(PreparedStatement pstmt,Connection con) throws Exception{
42         if(pstmt!=null){
43             pstmt.close();
44             if(con!=null){
45                 con.close();
46             }
47         }
48     }
49     
50 }

通用

（如图已经完成插入操作）

3:PreparedStatement更新操作

 1 package com.util;
 2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.PreparedStatement;
 6 import java.sql.Statement;
 7 
 8 public class Dbutil {
 9     
10     private static String url="jdbc:mysql://localhost:3306/db_person";//生命数据库的url(地址)
11     private static String user="root";//数据库账号
12     private static String password="123456";//数据库密码
13     private static String driver="com.mysql.jdbc.Driver";//数据库的驱动
14     
15     /**
16      * 
17      * @return
18      * @throws Exception
19      */
20     public Connection getCon() throws Exception{
21         Class.forName(driver);//加载数据库驱动
22         Connection con=DriverManager.getConnection(url, user, password);
23         //建立数据库的连接，获得连接对象con
24         return con;
25     }
26     
27     /**
28      * 
29      * @param con
30      * @throws Exception
31      */
32     public void close(Statement stmt,Connection con) throws Exception{
33         if(stmt!=null){
34             stmt.close();
35             if(con!=null){
36                 con.close();
37             }
38         }
39     }
40     
41     public void close(PreparedStatement pstmt,Connection con) throws Exception{
42         if(pstmt!=null){
43             pstmt.close();
44             if(con!=null){
45                 con.close();
46             }
47         }
48     }
49     
50 }

通用

 1 package com.ningmeng;
 2 
 3 public class Book {
 4     
 5     private int id;
 6     private String name;
 7     private float price;
 8     private String author;
 9     private int bookTypeId;
10     public String getName() {
11         return name;
12     }
13     public void setName(String name) {
14         this.name = name;
15     }
16     public float getPrice() {
17         return price;
18     }
19     public void setPrice(float price) {
20         this.price = price;
21     }
22     public String getAuthor() {
23         return author;
24     }
25     public void setAuthor(String author) {
26         this.author = author;
27     }
28     public int getBookTypeId() {
29         return bookTypeId;
30     }
31     public void setBookTypeId(int bookTypeId) {
32         this.bookTypeId = bookTypeId;
33     }
34     
35     public int getId() {
36         return id;
37     }
38     public void setId(int id) {
39         this.id = id;
40     }
41     /*public Book(String name, float price, String author, int bookTypeId) {
42         super();
43         this.name = name;
44         this.price = price;
45         this.author = author;
46         this.bookTypeId = bookTypeId;
47     }*/
48     public Book(int id, String name, float price, String author, int bookTypeId) {
49         super();
50         this.id = id;
51         this.name = name;
52         this.price = price;
53         this.author = author;
54         this.bookTypeId = bookTypeId;
55     }
56     
57     
58     
59     
60     
61     
62 }

封装

 1 package com.ningmeng;
 2 
 3 import java.sql.Connection;
 4 import java.sql.PreparedStatement;
 5 
 6 
 7 import com.util.Dbutil;
 8 
 9 public class Test6 {
10 
11     private static Dbutil db=new Dbutil();
12 
13     private static int add(Book book) throws Exception{
14         Connection con=db.getCon();//建立数据库的连接
15         String sql="update db_book set name=?,price=?,author=?,bookTypeId=? where id=?";//生成一条SQL语句
16         PreparedStatement pstmt=con.prepareStatement(sql);//创建Statement对象
17         pstmt.setString(1,book.getName());
18         pstmt.setFloat(2,book.getPrice());
19         pstmt.setString(3,book.getAuthor());
20         pstmt.setInt(4,book.getBookTypeId());
21         pstmt.setInt(5,book.getId());
22         int result=pstmt.executeUpdate();//执行SQL语句
23         db.close(pstmt,con);
24         return result;
25     }
26     public static void main(String[] args) throws Exception{
27         // TODO Auto-generated method stub
28         Book book =new Book(16,"javaweb",222,"小ma",1);
29         int result=add(book);
30         if(result==1){
31             System.out.println("更新成功");
32         }else{
33             System.out.println("更新失败");
34         }
35         
36     }
37 }

核心代码

（有图可见，已经完成更新操作）

4:PreparedStatement删除操作

 1 package com.ningmeng;
 2 
 3 import java.sql.Connection;
 4 import java.sql.PreparedStatement;
 5 
 6 import com.util.Dbutil;
 7 
 8 public class Test7 {
 9 
10     private static Dbutil db=new Dbutil();
11 
12     private static int add(Book book) throws Exception{
13         Connection con=db.getCon();//建立数据库的连接
14         String sql="delete from db_book where id=?";//生成一条SQL语句
15         PreparedStatement pstmt=con.prepareStatement(sql);//创建Statement对象
16         pstmt.setInt(1,book.getId());
17         int result=pstmt.executeUpdate();//执行SQL语句
18         db.close(pstmt,con);
19         return result;
20     }
21     public static void main(String[] args) throws Exception{
22         // TODO Auto-generated method stub
23         Book book =new Book(16);
24         int result=add(book);
25         if(result==1){
26             System.out.println("删除成功");
27         }else{
28             System.out.println("删除失败");
29         }
30         
31     }
32 }

核心代码

 1 package com.util;
 2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.PreparedStatement;
 6 import java.sql.Statement;
 7 
 8 public class Dbutil {
 9     
10     private static String url="jdbc:mysql://localhost:3306/db_person";//生命数据库的url(地址)
11     private static String user="root";//数据库账号
12     private static String password="123456";//数据库密码
13     private static String driver="com.mysql.jdbc.Driver";//数据库的驱动
14     
15     /**
16      * 
17      * @return
18      * @throws Exception
19      */
20     public Connection getCon() throws Exception{
21         Class.forName(driver);//加载数据库驱动
22         Connection con=DriverManager.getConnection(url, user, password);
23         //建立数据库的连接，获得连接对象con
24         return con;
25     }
26     
27     /**
28      * 
29      * @param con
30      * @throws Exception
31      */
32     public void close(Statement stmt,Connection con) throws Exception{
33         if(stmt!=null){
34             stmt.close();
35             if(con!=null){
36                 con.close();
37             }
38         }
39     }
40     
41     public void close(PreparedStatement pstmt,Connection con) throws Exception{
42         if(pstmt!=null){
43             pstmt.close();
44             if(con!=null){
45                 con.close();
46             }
47         }
48     }
49     
50 }

通用

 1 package com.ningmeng;
 2 
 3 public class Book {
 4     
 5     private int id;
 6     private String name;
 7     private float price;
 8     private String author;
 9     private int bookTypeId;
10     public String getName() {
11         return name;
12     }
13     public void setName(String name) {
14         this.name = name;
15     }
16     public float getPrice() {
17         return price;
18     }
19     public void setPrice(float price) {
20         this.price = price;
21     }
22     public String getAuthor() {
23         return author;
24     }
25     public void setAuthor(String author) {
26         this.author = author;
27     }
28     public int getBookTypeId() {
29         return bookTypeId;
30     }
31     public void setBookTypeId(int bookTypeId) {
32         this.bookTypeId = bookTypeId;
33     }
34     
35     public int getId() {
36         return id;
37     }
38     public void setId(int id) {
39         this.id = id;
40     }
41     /*public Book(String name, float price, String author, int bookTypeId) {
42         super();
43         this.name = name;
44         this.price = price;
45         this.author = author;
46         this.bookTypeId = bookTypeId;
47     }*/
48     /*public Book(int id, String name, float price, String author, int bookTypeId) {
49         super();
50         this.id = id;
51         this.name = name;
52         this.price = price;
53         this.author = author;
54         this.bookTypeId = bookTypeId;
55     }*/
56     public Book(int id) {
57         super();
58         this.id = id;
59     }
60     
61     
62     
63     
64     
65     
66 }

封装

（执行删除操作完成后如图）

1：ResultSet接口的使用

 1 package com.ningmeng;
 2 
 3 import java.sql.Connection;
 4 import java.sql.PreparedStatement;
 5 import java.sql.ResultSet;
 6 
 7 import com.util.Dbutil;
 8 
 9 public class Test8 {
10 
11     private static Dbutil db=new Dbutil();
12     
13     public static void select() throws Exception{
14         Connection con=db.getCon();
15         String sql="select * from db_book";
16         PreparedStatement pstmt=con.prepareStatement(sql);
17         ResultSet rs=pstmt.executeQuery();
18         while(rs.next()){
19             int id=rs.getInt(1);//id  开发的时候一般使用数据库字段名
20             String name=rs.getString(2);//name
21             float price=rs.getFloat(3);//price
22             String author=rs.getString(4);//author
23             int bookTypeId=rs.getInt(5);//bookTypeId
24             System.out.println(id+"\t"+name+"\t"+price+"\t"+author+"\t"+bookTypeId);
25         }
26         
27     }
28     public static void main(String[] args) throws Exception {
29         // TODO Auto-generated method stub
30         select();
31     }
32 
33 }

核心代码

 1 package com.util;
 2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.PreparedStatement;
 6 import java.sql.Statement;
 7 
 8 public class Dbutil {
 9     
10     private static String url="jdbc:mysql://localhost:3306/db_person";//生命数据库的url(地址)
11     private static String user="root";//数据库账号
12     private static String password="123456";//数据库密码
13     private static String driver="com.mysql.jdbc.Driver";//数据库的驱动
14     
15     /**
16      * 
17      * @return
18      * @throws Exception
19      */
20     public Connection getCon() throws Exception{
21         Class.forName(driver);//加载数据库驱动
22         Connection con=DriverManager.getConnection(url, user, password);
23         //建立数据库的连接，获得连接对象con
24         return con;
25     }
26     
27     /**
28      * 
29      * @param con
30      * @throws Exception
31      */
32     public void close(Statement stmt,Connection con) throws Exception{
33         if(stmt!=null){
34             stmt.close();
35             if(con!=null){
36                 con.close();
37             }
38         }
39     }
40     
41     public void close(PreparedStatement pstmt,Connection con) throws Exception{
42         if(pstmt!=null){
43             pstmt.close();
44             if(con!=null){
45                 con.close();
46             }
47         }
48     }
49     
50 }

通用

 1 package com.ningmeng;
 2 
 3 public class Book {
 4     
 5     private int id;
 6     private String name;
 7     private float price;
 8     private String author;
 9     private int bookTypeId;
10     public String getName() {
11         return name;
12     }
13     public void setName(String name) {
14         this.name = name;
15     }
16     public float getPrice() {
17         return price;
18     }
19     public void setPrice(float price) {
20         this.price = price;
21     }
22     public String getAuthor() {
23         return author;
24     }
25     public void setAuthor(String author) {
26         this.author = author;
27     }
28     public int getBookTypeId() {
29         return bookTypeId;
30     }
31     public void setBookTypeId(int bookTypeId) {
32         this.bookTypeId = bookTypeId;
33     }
34     
35     public int getId() {
36         return id;
37     }
38     public void setId(int id) {
39         this.id = id;
40     }
41     /*public Book(String name, float price, String author, int bookTypeId) {
42         super();
43         this.name = name;
44         this.price = price;
45         this.author = author;
46         this.bookTypeId = bookTypeId;
47     }*/
48     public Book(int id, String name, float price, String author, int bookTypeId) {
49         super();
50         this.id = id;
51         this.name = name;
52         this.price = price;
53         this.author = author;
54         this.bookTypeId = bookTypeId;
55     }
56     public Book(int id) {
57         super();
58         this.id = id;
59     }
60 
61 }

封装

2：以后开发使用的举例

 1 package com.ningmeng;
 2 
 3 
 4 import java.sql.Connection;
 5 import java.sql.PreparedStatement;
 6 import java.sql.ResultSet;
 7 import java.util.ArrayList;
 8 import java.util.List;
 9 
10 import com.util.Dbutil;
11 
12 public class Test9 {
13 
14     private static Dbutil db=new Dbutil();
15     
16     private static List<Book> select() throws Exception{
17         List<Book> list=new ArrayList<Book>();
18         Connection con=db.getCon();
19         String sql="select * from db_book";
20         PreparedStatement pstmt=con.prepareStatement(sql);
21         ResultSet rs=pstmt.executeQuery();
22         while(rs.next()){
23             int id=rs.getInt(1);//id  开发的时候一般使用数据库字段名
24             String name=rs.getString(2);//name
25             float price=rs.getFloat(3);//price
26             String author=rs.getString(4);//author
27             int bookTypeId=rs.getInt(5);//bookTypeId
28             Book book=new Book(id,name,price,author,bookTypeId);
29             list.add(book);
30         }
31         return list;
32         
33     }
34     public static void main(String[] args) throws Exception {
35         // TODO Auto-generated method stub
36         List<Book> list=select();
37         for(Book book : list){
38             System.out.println(book);
39         }
40     }
41 
42 }

核心代码

 1 package com.util;
 2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.PreparedStatement;
 6 import java.sql.Statement;
 7 
 8 public class Dbutil {
 9     
10     private static String url="jdbc:mysql://localhost:3306/db_person";//生命数据库的url(地址)
11     private static String user="root";//数据库账号
12     private static String password="123456";//数据库密码
13     private static String driver="com.mysql.jdbc.Driver";//数据库的驱动
14     
15     /**
16      * 
17      * @return
18      * @throws Exception
19      */
20     public Connection getCon() throws Exception{
21         Class.forName(driver);//加载数据库驱动
22         Connection con=DriverManager.getConnection(url, user, password);
23         //建立数据库的连接，获得连接对象con
24         return con;
25     }
26     
27     /**
28      * 
29      * @param con
30      * @throws Exception
31      */
32     public void close(Statement stmt,Connection con) throws Exception{
33         if(stmt!=null){
34             stmt.close();
35             if(con!=null){
36                 con.close();
37             }
38         }
39     }
40     
41     public void close(PreparedStatement pstmt,Connection con) throws Exception{
42         if(pstmt!=null){
43             pstmt.close();
44             if(con!=null){
45                 con.close();
46             }
47         }
48     }
49     
50 }

通用

 1 package com.ningmeng;
 2 
 3 public class Book {
 4     
 5     private int id;
 6     private String name;
 7     private float price;
 8     private String author;
 9     private int bookTypeId;
10     public String getName() {
11         return name;
12     }
13     public void setName(String name) {
14         this.name = name;
15     }
16     public float getPrice() {
17         return price;
18     }
19     public void setPrice(float price) {
20         this.price = price;
21     }
22     public String getAuthor() {
23         return author;
24     }
25     public void setAuthor(String author) {
26         this.author = author;
27     }
28     public int getBookTypeId() {
29         return bookTypeId;
30     }
31     public void setBookTypeId(int bookTypeId) {
32         this.bookTypeId = bookTypeId;
33     }
34     
35     public int getId() {
36         return id;
37     }
38     public void setId(int id) {
39         this.id = id;
40     }
41     /*public Book(String name, float price, String author, int bookTypeId) {
42         super();
43         this.name = name;
44         this.price = price;
45         this.author = author;
46         this.bookTypeId = bookTypeId;
47     }*/
48     public Book(int id, String name, float price, String author, int bookTypeId) {
49         super();
50         this.id = id;
51         this.name = name;
52         this.price = price;
53         this.author = author;
54         this.bookTypeId = bookTypeId;
55     }
56     public Book(int id) {
57         super();
58         this.id = id;
59     }
60     @Override
61     public String toString() {
62         return "Book [id=" + id + ", name=" + name + ", price=" + price + ", author=" + author + ", bookTypeId="
63                 + bookTypeId + "]";
64     }
65 
66     
67 }

封装

1:处理大数据对象

大数据对象处理主要有CLOB(character large object)和BLOB(binary large object)两种类型的字段；在CLOB中可以存储大字符数据对象，比如长篇小说；在BLOB中可以存放二进制大数据对象，比如图片，电影，音乐；

 1 package com.ningmeng;
 2 
 3 import java.io.File;
 4 import java.io.FileInputStream;
 5 import java.io.FileOutputStream;
 6 import java.io.InputStream;
 7 import java.sql.Blob;
 8 import java.sql.Clob;
 9 import java.sql.Connection;
10 import java.sql.PreparedStatement;
11 import java.sql.ResultSet;
12 
13 import com.util.Dbutil;
14 
15 public class Test10 {
16 
17     private static Dbutil db=new Dbutil();
18     
19     private static int add(Book book) throws Exception{
20         Connection con=db.getCon();//建立数据库的连接
21         String sql="insert into db_book values(null,?,?,?,?,?,?)";//生成一条SQL语句
22         PreparedStatement pstmt=con.prepareStatement(sql);//创建Statement对象
23         pstmt.setString(1,book.getName());
24         pstmt.setFloat(2,book.getPrice());
25         pstmt.setString(3,book.getAuthor());
26         pstmt.setInt(4,book.getBookTypeId());
27         
28         File context=book.getContext();
29         InputStream input=new FileInputStream(context);
30         pstmt.setAsciiStream(5, input,context.length());
31         
32         File pic=book.getPic();
33         InputStream input2=new FileInputStream(pic);
34         pstmt.setBinaryStream(6, input2,pic.length());
35         
36         
37         int result=pstmt.executeUpdate();//执行SQL语句
38         db.close(pstmt,con);
39         return result;
40     }
41     
42     public static void read(Book book) throws Exception{
43         Connection con=db.getCon();
44         String sql="select * from db_book where id="+book.getId();
45         PreparedStatement pstmt= con.prepareStatement(sql);
46         ResultSet rs=pstmt.executeQuery();
47         while(rs.next()){
48             int id=rs.getInt("id");
49             String name=rs.getString("name");
50             float price=rs.getFloat("price");
51             String author=rs.getString("author");
52             int bookTypeId=rs.getInt("bookTypeId");
53             Clob c=rs.getClob("context");
54             String context=c.getSubString(1,(int)c.length());
55             
56             Blob b=rs.getBlob("pic"); 
57             FileOutputStream fos=new FileOutputStream("G:/1.png");
58             fos.write(b.getBytes(1, (int) b.length()));
59             fos.close();
60                 
61             System.out.println("id"+"\t"+name+"\t"+price+"\t"+author+"\t"+bookTypeId+"\t"+context);
62             
63            
64         }
65         db.close(pstmt, con);
66     }
67     
68     public static void main(String[] args) throws Exception{
69         // TODO Auto-generated method stub
70         /*File context=new File("F:/子查询.txt");
71         File pic=new File("F:/1.png");
72         Book book=new Book("javaweb",888,"小别",1,context,pic);
73         int result=add(book);
74         if(result==1){
75             System.out.println("插入成功");
76         }else{
77             System.out.println("插入失败");
78         }*/
79         
80         Book book2=new Book(19);
81         read(book2);
82         
83         
84     }
85 
86 }

核心代码

  1 package com.ningmeng;
  2 
  3 import java.io.File;
  4 
  5 public class Book {
  6     
  7     private int id;
  8     private String name;
  9     private float price;
 10     private String author;
 11     private int bookTypeId;
 12     private File context;//处理文本
 13     private File pic;//处理图片的
 14     
 15     public String getName() {
 16         return name;
 17     }
 18     public void setName(String name) {
 19         this.name = name;
 20     }
 21     public float getPrice() {
 22         return price;
 23     }
 24     public void setPrice(float price) {
 25         this.price = price;
 26     }
 27     public String getAuthor() {
 28         return author;
 29     }
 30     public void setAuthor(String author) {
 31         this.author = author;
 32     }
 33     public int getBookTypeId() {
 34         return bookTypeId;
 35     }
 36     public void setBookTypeId(int bookTypeId) {
 37         this.bookTypeId = bookTypeId;
 38     }
 39     
 40     public int getId() {
 41         return id;
 42     }
 43     public void setId(int id) {
 44         this.id = id;
 45     }
 46     
 47     
 48     public File getContext() {
 49         return context;
 50     }
 51     public void setContext(File context) {
 52         this.context = context;
 53     }
 54     
 55     
 56     public File getPic() {
 57         return pic;
 58     }
 59     public void setPic(File pic) {
 60         this.pic = pic;
 61     }
 62     
 63     
 64     /*public Book(String name, float price, String author, int bookTypeId) {
 65         super();
 66         this.name = name;
 67         this.price = price;
 68         this.author = author;
 69         this.bookTypeId = bookTypeId;
 70     }*/
 71     public Book(int id, String name, float price, String author, int bookTypeId) {
 72         super();
 73         this.id = id;
 74         this.name = name;
 75         this.price = price;
 76         this.author = author;
 77         this.bookTypeId = bookTypeId;
 78     }
 79     public Book(int id) {
 80         super();
 81         this.id = id;
 82     }
 83     @Override
 84     public String toString() {
 85         return "Book [id=" + id + ", name=" + name + ", price=" + price + ", author=" + author + ", bookTypeId="
 86                 + bookTypeId + "]";
 87     }
 88     public Book(String name, float price, String author, int bookTypeId, File context) {
 89         super();
 90         this.name = name;
 91         this.price = price;
 92         this.author = author;
 93         this.bookTypeId = bookTypeId;
 94         this.context = context;
 95     }
 96     public Book(String name, float price, String author, int bookTypeId, File context, File pic) {
 97         super();
 98         this.name = name;
 99         this.price = price;
100         this.author = author;
101         this.bookTypeId = bookTypeId;
102         this.context = context;
103         this.pic = pic;
104     }
105 
106     
107     
108     //构造方法是根据不同的初始化对象的需要构造的
109 }

封装

 1 package com.util;
 2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.PreparedStatement;
 6 import java.sql.Statement;
 7 
 8 public class Dbutil {
 9     
10     private static String url="jdbc:mysql://localhost:3306/db_person";//生命数据库的url(地址)
11     private static String user="root";//数据库账号
12     private static String password="123456";//数据库密码
13     private static String driver="com.mysql.jdbc.Driver";//数据库的驱动
14     
15     /**
16      * 
17      * @return
18      * @throws Exception
19      */
20     public Connection getCon() throws Exception{
21         Class.forName(driver);//加载数据库驱动
22         Connection con=DriverManager.getConnection(url, user, password);
23         //建立数据库的连接，获得连接对象con
24         return con;
25     }
26     
27     /**
28      * 
29      * @param con
30      * @throws Exception
31      */
32     public void close(Statement stmt,Connection con) throws Exception{
33         if(stmt!=null){
34             stmt.close();
35             if(con!=null){
36                 con.close();
37             }
38         }
39     }
40     
41     public void close(PreparedStatement pstmt,Connection con) throws Exception{
42         if(pstmt!=null){
43             pstmt.close();
44             if(con!=null){
45                 con.close();
46             }
47         }
48     }
49     
50 }

通用

码农公寓

相关文章