Eclipse中java向数据库中添加数据，更新数据，删除数据 |
您所在的位置：网站首页 › java向数据库中添加数据 › Eclipse中java向数据库中添加数据，更新数据，删除数据 |
Eclipse中java向数据库中添加数据，更新数据，删除数据
|
前面详细写过如何连接数据库的具体操作，下面介绍向数据库中添加数据。 注意事项：如果参考下面代码，需要 改包名，数据库名，数据库账号，密码，和数据表（数据表里面的信息） 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 select() throws Exception{
17 List list=new ArrayList();
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 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 }
通用
|
【本文地址】
今日新闻 |
推荐新闻 |