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

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

注意事项:如果参考下面代码,需要

改包名,数据库名,数据库账号,密码,和数据表(数据表里面的信息)

 package com.ningmeng;

 import java.sql.*;

 /**
* 1:向数据库中添加数据
* @author biexiansheng
*
*/
public class Test01 { public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");//加载数据库驱动
System.out.println("加载数据库驱动成功");
String url="jdbc:mysql://localhost:3306/test";//声明数据库test的url
String user="root";//数据库的用户名
String password="123456";//数据库的密码
//建立数据库连接,获得连接对象conn(抛出异常即可)
Connection conn=DriverManager.getConnection(url, user, password);
System.out.println("连接数据库成功");
//生成一条mysql语句
String sql="insert into users(username,password,age,sex) values('小别','123456',22,0)";
Statement stmt=conn.createStatement();//创建一个Statement对象
stmt.executeUpdate(sql);//执行sql语句
System.out.println("插入到数据库成功");
conn.close();
System.out.println("关闭数据库成功");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}//
catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} } }

详细运行结果

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

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

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

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

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

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

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

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

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

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

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

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




第二种方法

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

 package com.ningmeng;

 import java.sql.*;

 /**
* 1:使用PreparedStatement接口来执行插入语句
*
* @author biexiansheng
*
*/
public class Test02 { public static void main(String[] args) {
// TODO Auto-generated method stub
try {
Class.forName("com.mysql.jdbc.Driver");//加载数据库驱动
System.out.println("加载数据库驱动成功");
String url="jdbc:mysql://localhost:3306/test";//声明数据库test的url
String user="root";//数据库用户名
String password="123456";//数据库密码
//建立数据库连接,获得连接对象conn
Connection conn=DriverManager.getConnection(url, user, password);
System.out.println("连接数据库驱动成功");
//生成一条SQL语句
String sql="insert into users(username,password,age,sex) values(?,?,?,?)";
PreparedStatement ps=conn.prepareStatement(sql);//创建一个Statement对象
ps.setNString(1,"lisi");//为sql语句中第一个问号赋值
ps.setString(2,"123456");//为sql语句中第二个问号赋值
ps.setInt(3,24);//为sql语句第三个问号赋值
ps.setInt(4,2);//为sql语句的第四个问号赋值
ps.executeUpdate();//执行sql语句
conn.close();//关闭数据库连接对象
System.out.println("关闭数据库连接对象");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} } }

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

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

由于刚才不小心多执行了一遍第一个程序,所以多了一行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数据库账号,密码,数据库名,数据表名,字段等等。)

 package com.util;

 import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException; public class Dbutil { private static String url="jdbc:mysql://localhost:3306/db_person";//生命数据库的url(地址)
private static String user="root";//数据库账号
private static String password="123456";//数据库密码
private static String driver="com.mysql.jdbc.Driver";//数据库的驱动 /**
*
* @return
* @throws Exception
*/
public Connection getCon() throws Exception{
Class.forName(driver);//加载数据库驱动
Connection con=DriverManager.getConnection(url, user, password);
//建立数据库的连接,获得连接对象con
return con;
} /**
*
* @param con
* @throws Exception
*/
public void close(Connection con) throws Exception{
if(con!=null){
con.close();
}
} }

封装的通用的一些东西

 package com.ningmeng;

 import java.sql.Connection;
import java.sql.Statement; import com.util.Dbutil; public class Test { public static void main(String args[]) throws Exception {
Dbutil db=new Dbutil();
String sql="insert into db_book values(null,'javaweb',888,'小别',1)";//生成一条sql语句
Connection con=db.getCon();//获取数据库的连接
Statement stmt=con.createStatement();//创建一个Statement连接
int result=stmt.executeUpdate(sql);//执行sql语句
System.out.println("执行了"+result+"数据");
stmt.close();//关闭顺序是先关闭小的,后关闭大的
con.close(); }
}

核心代码

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

(插入数据时使用拼接)同样引用上面封装的通用的一些东西!!!

 package com.ningmeng;

 import java.sql.Connection;
import java.sql.Statement; import com.util.Dbutil; public class Test2 { private static Dbutil db=new Dbutil();
private static int add(String name,float price,String author,int bookTypeId)
throws Exception{
Connection con=db.getCon();
String sql="insert into db_book values(null,'"+name+"',"+price+",'"+author+"',"+bookTypeId+")";
Statement stmt=con.createStatement();//创建一个Statement连接
int result=stmt.executeUpdate(sql);//执行sql语句
db.close(stmt,con);
return result;
}
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
int result=add("java",888,"小卡",1);
if(result==1){
System.out.println("添加成功");
}else{
System.out.println("添加失败");
} } }

核心代码

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

(可以发现已经添加成功了)

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

3:下面使用面向对象的思想传入数据(实现的时候和上面的一同完成操作,)

 package com.ningmeng;

 public class Book {

     private String name;
private float price;
private String author;
private int bookTypeId;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public float getPrice() {
return price;
}
public void setPrice(float price) {
this.price = price;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public int getBookTypeId() {
return bookTypeId;
}
public void setBookTypeId(int bookTypeId) {
this.bookTypeId = bookTypeId;
}
public Book(String name, float price, String author, int bookTypeId) {
super();
this.name = name;
this.price = price;
this.author = author;
this.bookTypeId = bookTypeId;
} }

封装,面向对象的思想

 package com.ningmeng;

 import java.sql.Connection;
import java.sql.Statement; import com.util.Dbutil; public class Test2 { private static Dbutil db=new Dbutil(); private static int add2(Book book) throws Exception{
Connection con=db.getCon();
String sql="insert into db_book values(null,'"+book.getName()+"',"+book.getPrice()+",'"+book.getAuthor()+"',"+book.getBookTypeId()+")";
Statement stmt=con.createStatement();//创建一个Statement连接
int result=stmt.executeUpdate(sql);//执行sql语句
db.close(stmt,con);
return result;
} private static int add(String name,float price,String author,int bookTypeId)
throws Exception{
Connection con=db.getCon();
String sql="insert into db_book values(null,'"+name+"',"+price+",'"+author+"',"+bookTypeId+")";
Statement stmt=con.createStatement();//创建一个Statement连接
int result=stmt.executeUpdate(sql);//执行sql语句
db.close(stmt,con);
return result;
}
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
/*int result=add("java",888,"小卡",1);
if(result==1){
System.out.println("添加成功");
}else{
System.out.println("添加失败");
}*/ Book book=new Book("java2",888,"小卡2",1);
int result=add2(book);
if(result==1){
System.out.println("添加成功");
}else{
System.out.println("添加失败");
} }
}

核心代码

 package com.util;

 import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement; public class Dbutil { private static String url="jdbc:mysql://localhost:3306/db_person";//生命数据库的url(地址)
private static String user="root";//数据库账号
private static String password="123456";//数据库密码
private static String driver="com.mysql.jdbc.Driver";//数据库的驱动 /**
*
* @return
* @throws Exception
*/
public Connection getCon() throws Exception{
Class.forName(driver);//加载数据库驱动
Connection con=DriverManager.getConnection(url, user, password);
//建立数据库的连接,获得连接对象con
return con;
} /**
*
* @param con
* @throws Exception
*/
public void close(Statement stmt,Connection con) throws Exception{
if(stmt!=null){
stmt.close();
if(con!=null){
con.close();
}
} } }

通用的




 1:使用Statement更新操作

 package com.ningmeng;

 import java.sql.Connection;
import java.sql.Statement; import com.util.Dbutil; /**
* 更新操作
* @author biexiansheng
*
*/
public class Test3 { private static Dbutil db=new Dbutil(); private static int update(Book book) throws Exception{
Connection con=db.getCon();
String sql="update db_book set name='"+book.getName()+"',price="+book.getPrice()+",author='"+book.getAuthor()+"',bookTypeId="+book.getBookTypeId()+" where id=13";
Statement stmt=con.createStatement();//创建一个Statement连接
int result=stmt.executeUpdate(sql);//执行sql语句
db.close(stmt,con);
return result;
} public static void main(String[] args) throws Exception {
Book book=new Book(13,"java120",666,"小别",1);
int result=update(book);
if(result==1){
System.out.println("更新成功");
}else{
System.out.println("更新失败");
}
}
}

核心代码

 package com.util;

 import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement; public class Dbutil { private static String url="jdbc:mysql://localhost:3306/db_person";//生命数据库的url(地址)
private static String user="root";//数据库账号
private static String password="123456";//数据库密码
private static String driver="com.mysql.jdbc.Driver";//数据库的驱动 /**
*
* @return
* @throws Exception
*/
public Connection getCon() throws Exception{
Class.forName(driver);//加载数据库驱动
Connection con=DriverManager.getConnection(url, user, password);
//建立数据库的连接,获得连接对象con
return con;
} /**
*
* @param con
* @throws Exception
*/
public void close(Statement stmt,Connection con) throws Exception{
if(stmt!=null){
stmt.close();
if(con!=null){
con.close();
}
} } }

通用代码

 package com.ningmeng;

 public class Book {

     private int id;
private String name;
private float price;
private String author;
private int bookTypeId;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public float getPrice() {
return price;
}
public void setPrice(float price) {
this.price = price;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public int getBookTypeId() {
return bookTypeId;
}
public void setBookTypeId(int bookTypeId) {
this.bookTypeId = bookTypeId;
} public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
} public Book(int id, String name, float price, String author, int bookTypeId) {
super();
this.id = id;
this.name = name;
this.price = price;
this.author = author;
this.bookTypeId = bookTypeId;
} }

封装代码

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

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

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




1:使用Statement执行删除操作

 package com.ningmeng;

 import java.sql.Connection;
import java.sql.Statement; import com.util.Dbutil; public class Test4 { private static Dbutil db=new Dbutil(); private static int delete(Book book) throws Exception{
Connection con=db.getCon();
String sql="delete from db_book where id="+book.getId();
Statement stmt=con.createStatement();//创建一个Statement连接
int result=stmt.executeUpdate(sql);//执行sql语句
db.close(stmt,con);
return result;
}
public static void main(String[] args) throws Exception{
// TODO Auto-generated method stub
Book book=new Book(13);
int result=delete(book);
if(result==1){
System.out.println("删除成功");
}else{
System.out.println("删除失败");
}
} }

核心代码

 package com.util;

 import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement; public class Dbutil { private static String url="jdbc:mysql://localhost:3306/db_person";//生命数据库的url(地址)
private static String user="root";//数据库账号
private static String password="123456";//数据库密码
private static String driver="com.mysql.jdbc.Driver";//数据库的驱动 /**
*
* @return
* @throws Exception
*/
public Connection getCon() throws Exception{
Class.forName(driver);//加载数据库驱动
Connection con=DriverManager.getConnection(url, user, password);
//建立数据库的连接,获得连接对象con
return con;
} /**
*
* @param con
* @throws Exception
*/
public void close(Statement stmt,Connection con) throws Exception{
if(stmt!=null){
stmt.close();
if(con!=null){
con.close();
}
} } }

通用的

 package com.ningmeng;

 public class Book {

     private int id;
private String name;
private float price;
private String author;
private int bookTypeId;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public float getPrice() {
return price;
}
public void setPrice(float price) {
this.price = price;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public int getBookTypeId() {
return bookTypeId;
}
public void setBookTypeId(int bookTypeId) {
this.bookTypeId = bookTypeId;
} public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public Book(int id) {
super();
this.id = id;
} }

封装

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

(可以看到删除操作执行完毕。)

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





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

2:PreparedStatement插入操作

 package com.ningmeng;

 import java.sql.Connection;
import java.sql.PreparedStatement; import com.util.Dbutil; public class Test5 { private static Dbutil db=new Dbutil(); private static int add(Book book) throws Exception{
Connection con=db.getCon();//建立数据库的连接
String sql="insert into db_book values(null,?,?,?,?)";//生成一条SQL语句
PreparedStatement pstmt=con.prepareStatement(sql);//创建Statement对象
pstmt.setString(1,book.getName());
pstmt.setFloat(2,book.getPrice());
pstmt.setString(3,book.getAuthor());
pstmt.setInt(4,book.getBookTypeId());
int result=pstmt.executeUpdate();//执行SQL语句
db.close(pstmt,con);
return result;
}
public static void main(String[] args) throws Exception{
// TODO Auto-generated method stub
Book book =new Book("openstack",999,"小别",1);
int result=add(book);
if(result==1){
System.out.println("插入成功");
}else{
System.out.println("插入失败");
}
} }

核心代码

 package com.ningmeng;

 public class Book {

     private int id;
private String name;
private float price;
private String author;
private int bookTypeId;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public float getPrice() {
return price;
}
public void setPrice(float price) {
this.price = price;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public int getBookTypeId() {
return bookTypeId;
}
public void setBookTypeId(int bookTypeId) {
this.bookTypeId = bookTypeId;
} public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public Book(String name, float price, String author, int bookTypeId) {
super();
this.name = name;
this.price = price;
this.author = author;
this.bookTypeId = bookTypeId;
} }

封装

 package com.util;

 import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement; public class Dbutil { private static String url="jdbc:mysql://localhost:3306/db_person";//生命数据库的url(地址)
private static String user="root";//数据库账号
private static String password="123456";//数据库密码
private static String driver="com.mysql.jdbc.Driver";//数据库的驱动 /**
*
* @return
* @throws Exception
*/
public Connection getCon() throws Exception{
Class.forName(driver);//加载数据库驱动
Connection con=DriverManager.getConnection(url, user, password);
//建立数据库的连接,获得连接对象con
return con;
} /**
*
* @param con
* @throws Exception
*/
public void close(Statement stmt,Connection con) throws Exception{
if(stmt!=null){
stmt.close();
if(con!=null){
con.close();
}
}
} public void close(PreparedStatement pstmt,Connection con) throws Exception{
if(pstmt!=null){
pstmt.close();
if(con!=null){
con.close();
}
}
} }

通用

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

(如图已经完成插入操作)

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

3:PreparedStatement更新操作

 package com.util;

 import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement; public class Dbutil { private static String url="jdbc:mysql://localhost:3306/db_person";//生命数据库的url(地址)
private static String user="root";//数据库账号
private static String password="123456";//数据库密码
private static String driver="com.mysql.jdbc.Driver";//数据库的驱动 /**
*
* @return
* @throws Exception
*/
public Connection getCon() throws Exception{
Class.forName(driver);//加载数据库驱动
Connection con=DriverManager.getConnection(url, user, password);
//建立数据库的连接,获得连接对象con
return con;
} /**
*
* @param con
* @throws Exception
*/
public void close(Statement stmt,Connection con) throws Exception{
if(stmt!=null){
stmt.close();
if(con!=null){
con.close();
}
}
} public void close(PreparedStatement pstmt,Connection con) throws Exception{
if(pstmt!=null){
pstmt.close();
if(con!=null){
con.close();
}
}
} }

通用

 package com.ningmeng;

 public class Book {

     private int id;
private String name;
private float price;
private String author;
private int bookTypeId;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public float getPrice() {
return price;
}
public void setPrice(float price) {
this.price = price;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public int getBookTypeId() {
return bookTypeId;
}
public void setBookTypeId(int bookTypeId) {
this.bookTypeId = bookTypeId;
} public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
/*public Book(String name, float price, String author, int bookTypeId) {
super();
this.name = name;
this.price = price;
this.author = author;
this.bookTypeId = bookTypeId;
}*/
public Book(int id, String name, float price, String author, int bookTypeId) {
super();
this.id = id;
this.name = name;
this.price = price;
this.author = author;
this.bookTypeId = bookTypeId;
} }

封装

 package com.ningmeng;

 import java.sql.Connection;
import java.sql.PreparedStatement; import com.util.Dbutil; public class Test6 { private static Dbutil db=new Dbutil(); private static int add(Book book) throws Exception{
Connection con=db.getCon();//建立数据库的连接
String sql="update db_book set name=?,price=?,author=?,bookTypeId=? where id=?";//生成一条SQL语句
PreparedStatement pstmt=con.prepareStatement(sql);//创建Statement对象
pstmt.setString(1,book.getName());
pstmt.setFloat(2,book.getPrice());
pstmt.setString(3,book.getAuthor());
pstmt.setInt(4,book.getBookTypeId());
pstmt.setInt(5,book.getId());
int result=pstmt.executeUpdate();//执行SQL语句
db.close(pstmt,con);
return result;
}
public static void main(String[] args) throws Exception{
// TODO Auto-generated method stub
Book book =new Book(16,"javaweb",222,"小ma",1);
int result=add(book);
if(result==1){
System.out.println("更新成功");
}else{
System.out.println("更新失败");
} }
}

核心代码

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

(有图可见,已经完成更新操作)

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

4:PreparedStatement删除操作

 package com.ningmeng;

 import java.sql.Connection;
import java.sql.PreparedStatement; import com.util.Dbutil; public class Test7 { private static Dbutil db=new Dbutil(); private static int add(Book book) throws Exception{
Connection con=db.getCon();//建立数据库的连接
String sql="delete from db_book where id=?";//生成一条SQL语句
PreparedStatement pstmt=con.prepareStatement(sql);//创建Statement对象
pstmt.setInt(1,book.getId());
int result=pstmt.executeUpdate();//执行SQL语句
db.close(pstmt,con);
return result;
}
public static void main(String[] args) throws Exception{
// TODO Auto-generated method stub
Book book =new Book(16);
int result=add(book);
if(result==1){
System.out.println("删除成功");
}else{
System.out.println("删除失败");
} }
}

核心代码

 package com.util;

 import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement; public class Dbutil { private static String url="jdbc:mysql://localhost:3306/db_person";//生命数据库的url(地址)
private static String user="root";//数据库账号
private static String password="123456";//数据库密码
private static String driver="com.mysql.jdbc.Driver";//数据库的驱动 /**
*
* @return
* @throws Exception
*/
public Connection getCon() throws Exception{
Class.forName(driver);//加载数据库驱动
Connection con=DriverManager.getConnection(url, user, password);
//建立数据库的连接,获得连接对象con
return con;
} /**
*
* @param con
* @throws Exception
*/
public void close(Statement stmt,Connection con) throws Exception{
if(stmt!=null){
stmt.close();
if(con!=null){
con.close();
}
}
} public void close(PreparedStatement pstmt,Connection con) throws Exception{
if(pstmt!=null){
pstmt.close();
if(con!=null){
con.close();
}
}
} }

通用

 package com.ningmeng;

 public class Book {

     private int id;
private String name;
private float price;
private String author;
private int bookTypeId;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public float getPrice() {
return price;
}
public void setPrice(float price) {
this.price = price;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public int getBookTypeId() {
return bookTypeId;
}
public void setBookTypeId(int bookTypeId) {
this.bookTypeId = bookTypeId;
} public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
/*public Book(String name, float price, String author, int bookTypeId) {
super();
this.name = name;
this.price = price;
this.author = author;
this.bookTypeId = bookTypeId;
}*/
/*public Book(int id, String name, float price, String author, int bookTypeId) {
super();
this.id = id;
this.name = name;
this.price = price;
this.author = author;
this.bookTypeId = bookTypeId;
}*/
public Book(int id) {
super();
this.id = id;
} }

封装

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

(执行删除操作完成后如图)

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





1:ResultSet接口的使用

 package com.ningmeng;

 import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet; import com.util.Dbutil; public class Test8 { private static Dbutil db=new Dbutil(); public static void select() throws Exception{
Connection con=db.getCon();
String sql="select * from db_book";
PreparedStatement pstmt=con.prepareStatement(sql);
ResultSet rs=pstmt.executeQuery();
while(rs.next()){
int id=rs.getInt(1);//id 开发的时候一般使用数据库字段名
String name=rs.getString(2);//name
float price=rs.getFloat(3);//price
String author=rs.getString(4);//author
int bookTypeId=rs.getInt(5);//bookTypeId
System.out.println(id+"\t"+name+"\t"+price+"\t"+author+"\t"+bookTypeId);
} }
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
select();
} }

核心代码

 package com.util;

 import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement; public class Dbutil { private static String url="jdbc:mysql://localhost:3306/db_person";//生命数据库的url(地址)
private static String user="root";//数据库账号
private static String password="123456";//数据库密码
private static String driver="com.mysql.jdbc.Driver";//数据库的驱动 /**
*
* @return
* @throws Exception
*/
public Connection getCon() throws Exception{
Class.forName(driver);//加载数据库驱动
Connection con=DriverManager.getConnection(url, user, password);
//建立数据库的连接,获得连接对象con
return con;
} /**
*
* @param con
* @throws Exception
*/
public void close(Statement stmt,Connection con) throws Exception{
if(stmt!=null){
stmt.close();
if(con!=null){
con.close();
}
}
} public void close(PreparedStatement pstmt,Connection con) throws Exception{
if(pstmt!=null){
pstmt.close();
if(con!=null){
con.close();
}
}
} }

通用

 package com.ningmeng;

 public class Book {

     private int id;
private String name;
private float price;
private String author;
private int bookTypeId;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public float getPrice() {
return price;
}
public void setPrice(float price) {
this.price = price;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public int getBookTypeId() {
return bookTypeId;
}
public void setBookTypeId(int bookTypeId) {
this.bookTypeId = bookTypeId;
} public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
/*public Book(String name, float price, String author, int bookTypeId) {
super();
this.name = name;
this.price = price;
this.author = author;
this.bookTypeId = bookTypeId;
}*/
public Book(int id, String name, float price, String author, int bookTypeId) {
super();
this.id = id;
this.name = name;
this.price = price;
this.author = author;
this.bookTypeId = bookTypeId;
}
public Book(int id) {
super();
this.id = id;
} }

封装

2:以后开发使用的举例

 package com.ningmeng;

 import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List; import com.util.Dbutil; public class Test9 { private static Dbutil db=new Dbutil(); private static List<Book> select() throws Exception{
List<Book> list=new ArrayList<Book>();
Connection con=db.getCon();
String sql="select * from db_book";
PreparedStatement pstmt=con.prepareStatement(sql);
ResultSet rs=pstmt.executeQuery();
while(rs.next()){
int id=rs.getInt(1);//id 开发的时候一般使用数据库字段名
String name=rs.getString(2);//name
float price=rs.getFloat(3);//price
String author=rs.getString(4);//author
int bookTypeId=rs.getInt(5);//bookTypeId
Book book=new Book(id,name,price,author,bookTypeId);
list.add(book);
}
return list; }
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
List<Book> list=select();
for(Book book : list){
System.out.println(book);
}
} }

核心代码

 package com.util;

 import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement; public class Dbutil { private static String url="jdbc:mysql://localhost:3306/db_person";//生命数据库的url(地址)
private static String user="root";//数据库账号
private static String password="123456";//数据库密码
private static String driver="com.mysql.jdbc.Driver";//数据库的驱动 /**
*
* @return
* @throws Exception
*/
public Connection getCon() throws Exception{
Class.forName(driver);//加载数据库驱动
Connection con=DriverManager.getConnection(url, user, password);
//建立数据库的连接,获得连接对象con
return con;
} /**
*
* @param con
* @throws Exception
*/
public void close(Statement stmt,Connection con) throws Exception{
if(stmt!=null){
stmt.close();
if(con!=null){
con.close();
}
}
} public void close(PreparedStatement pstmt,Connection con) throws Exception{
if(pstmt!=null){
pstmt.close();
if(con!=null){
con.close();
}
}
} }

通用

 package com.ningmeng;

 public class Book {

     private int id;
private String name;
private float price;
private String author;
private int bookTypeId;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public float getPrice() {
return price;
}
public void setPrice(float price) {
this.price = price;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public int getBookTypeId() {
return bookTypeId;
}
public void setBookTypeId(int bookTypeId) {
this.bookTypeId = bookTypeId;
} public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
/*public Book(String name, float price, String author, int bookTypeId) {
super();
this.name = name;
this.price = price;
this.author = author;
this.bookTypeId = bookTypeId;
}*/
public Book(int id, String name, float price, String author, int bookTypeId) {
super();
this.id = id;
this.name = name;
this.price = price;
this.author = author;
this.bookTypeId = bookTypeId;
}
public Book(int id) {
super();
this.id = id;
}
@Override
public String toString() {
return "Book [id=" + id + ", name=" + name + ", price=" + price + ", author=" + author + ", bookTypeId="
+ bookTypeId + "]";
} }

封装





1:处理大数据对象

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

 package com.ningmeng;

 import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet; import com.util.Dbutil; public class Test10 { private static Dbutil db=new Dbutil(); private static int add(Book book) throws Exception{
Connection con=db.getCon();//建立数据库的连接
String sql="insert into db_book values(null,?,?,?,?,?,?)";//生成一条SQL语句
PreparedStatement pstmt=con.prepareStatement(sql);//创建Statement对象
pstmt.setString(1,book.getName());
pstmt.setFloat(2,book.getPrice());
pstmt.setString(3,book.getAuthor());
pstmt.setInt(4,book.getBookTypeId()); File context=book.getContext();
InputStream input=new FileInputStream(context);
pstmt.setAsciiStream(5, input,context.length()); File pic=book.getPic();
InputStream input2=new FileInputStream(pic);
pstmt.setBinaryStream(6, input2,pic.length()); int result=pstmt.executeUpdate();//执行SQL语句
db.close(pstmt,con);
return result;
} public static void read(Book book) throws Exception{
Connection con=db.getCon();
String sql="select * from db_book where id="+book.getId();
PreparedStatement pstmt= con.prepareStatement(sql);
ResultSet rs=pstmt.executeQuery();
while(rs.next()){
int id=rs.getInt("id");
String name=rs.getString("name");
float price=rs.getFloat("price");
String author=rs.getString("author");
int bookTypeId=rs.getInt("bookTypeId");
Clob c=rs.getClob("context");
String context=c.getSubString(1,(int)c.length()); Blob b=rs.getBlob("pic");
FileOutputStream fos=new FileOutputStream("G:/1.png");
fos.write(b.getBytes(1, (int) b.length()));
fos.close(); System.out.println("id"+"\t"+name+"\t"+price+"\t"+author+"\t"+bookTypeId+"\t"+context); }
db.close(pstmt, con);
} public static void main(String[] args) throws Exception{
// TODO Auto-generated method stub
/*File context=new File("F:/子查询.txt");
File pic=new File("F:/1.png");
Book book=new Book("javaweb",888,"小别",1,context,pic);
int result=add(book);
if(result==1){
System.out.println("插入成功");
}else{
System.out.println("插入失败");
}*/ Book book2=new Book(19);
read(book2); } }

核心代码

 package com.ningmeng;

 import java.io.File;

 public class Book {

     private int id;
private String name;
private float price;
private String author;
private int bookTypeId;
private File context;//处理文本
private File pic;//处理图片的 public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public float getPrice() {
return price;
}
public void setPrice(float price) {
this.price = price;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public int getBookTypeId() {
return bookTypeId;
}
public void setBookTypeId(int bookTypeId) {
this.bookTypeId = bookTypeId;
} public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
} public File getContext() {
return context;
}
public void setContext(File context) {
this.context = context;
} public File getPic() {
return pic;
}
public void setPic(File pic) {
this.pic = pic;
} /*public Book(String name, float price, String author, int bookTypeId) {
super();
this.name = name;
this.price = price;
this.author = author;
this.bookTypeId = bookTypeId;
}*/
public Book(int id, String name, float price, String author, int bookTypeId) {
super();
this.id = id;
this.name = name;
this.price = price;
this.author = author;
this.bookTypeId = bookTypeId;
}
public Book(int id) {
super();
this.id = id;
}
@Override
public String toString() {
return "Book [id=" + id + ", name=" + name + ", price=" + price + ", author=" + author + ", bookTypeId="
+ bookTypeId + "]";
}
public Book(String name, float price, String author, int bookTypeId, File context) {
super();
this.name = name;
this.price = price;
this.author = author;
this.bookTypeId = bookTypeId;
this.context = context;
}
public Book(String name, float price, String author, int bookTypeId, File context, File pic) {
super();
this.name = name;
this.price = price;
this.author = author;
this.bookTypeId = bookTypeId;
this.context = context;
this.pic = pic;
} //构造方法是根据不同的初始化对象的需要构造的
}

封装

 package com.util;

 import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement; public class Dbutil { private static String url="jdbc:mysql://localhost:3306/db_person";//生命数据库的url(地址)
private static String user="root";//数据库账号
private static String password="123456";//数据库密码
private static String driver="com.mysql.jdbc.Driver";//数据库的驱动 /**
*
* @return
* @throws Exception
*/
public Connection getCon() throws Exception{
Class.forName(driver);//加载数据库驱动
Connection con=DriverManager.getConnection(url, user, password);
//建立数据库的连接,获得连接对象con
return con;
} /**
*
* @param con
* @throws Exception
*/
public void close(Statement stmt,Connection con) throws Exception{
if(stmt!=null){
stmt.close();
if(con!=null){
con.close();
}
}
} public void close(PreparedStatement pstmt,Connection con) throws Exception{
if(pstmt!=null){
pstmt.close();
if(con!=null){
con.close();
}
}
} }

通用

上一篇:转:Ubuntu下下载工具安装--uget+aria2


下一篇:LINUX系统学习以及初学者系统下载