注意事项：如果参考下面代码，需要
改包名，数据库名，数据库账号，密码，和数据表（数据表里面的信息）
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 } }
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 } }
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 }
