我们假设有如下数据集:
现有图书信息表bookinfo(序号,图书编号,图书名称,作者,出版社,单价,出版日期,ISBN,库存数量)。
通过java程序实现对该表的增删改查操作。
软件准备:sql_server2008
环境:java环境(推荐eclipse)
这次我们采用自制的数据集(不需要多大的数据量):
我们打开如下软件:
再然后按下图一步步执行
需要插入的代码片段如下:
create table Bookinfo
(
number int ,
Book_Id varchar(10) primary key ,
Book_Name varchar(20) not null ,
Book_Author varchar(20) ,
Book_Publisher varchar(20) ,
Book_Price int not null ,
Book_data varchar(10) ,
Book_ISBN varchar(20) ,
Book_Quantity int
);
insert into Bookinfo values(1,'0001','数据结构','Monkey·D·Luffy','东京出版社',100,'2000-1-1','xxx1',1);
insert into Bookinfo values(2,'0002','计算机网络','Roronoa Zoro','东京出版社',100,'2000-1-1','xxx2',1);
insert into Bookinfo values(3,'0003','Java','Nami','东京出版社',100,'2000-1-1','xxx3',1);
insert into Bookinfo values(4,'0004','HTML','Sanji','东京出版社',100,'2000-1-1','xxx4',1);
insert into Bookinfo values(5,'0005','python','Usopp Sogeking','东京出版社',100,'2000-1-1','xxx5',1);
insert into Bookinfo values(6,'0006','C++',' Tony Tony Chopper','东京出版社',100,'2000-1-1','xxx6',1);
insert into Bookinfo values(7,'0007','C#','Nico Robin','东京出版社',100,'2000-1-1','xxx7',1);
insert into Bookinfo values(8,'0008','Mysql','FRANKY','东京出版社',100,'2000-1-1','xxx8',1);
insert into Bookinfo values(9,'0009','CV','BROOK','东京出版社',100,'2000-1-1','xxx9',1);
insert into Bookinfo values(10,'0010','NLP','Jimbei','东京出版社',100,'2000-1-1','xxx10',1);
预览数据:
以上是对数据库的操作(如果已经有自己的数据的话,可以忽略以上操作)
接下来是java编程连接数据库了(我也参考了网上不少方法,下面是自己实测通过的方法之一)
我们先运行如下java代码(注意按注释做相应修改):
如果显示数据库连接成功,就没有问题,可以看下一步。如果失败的话,参考以下连接:Java连接SQL Server 2008
import java.sql.*;
public class Sql_Conect {
public static void main(String[] args) {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String url = "jdbc:sqlserver://127.0.0.1:1433;databaseName=My_DataBase";
//databaseName 后改为自己的数据库名字
Connection con = DriverManager.getConnection(url,"sa","123456");
// sa是用户名,12345是数据库密码
System.out.println("数据库连接成功");
con.close(); // 关闭数据库访问
}
catch(Exception e) {
System.out.println("数据库连接失败\n" + e.toString());
}
}
}
接下来我们开始用java实现sql的增删改查功能(要求读者先了解一些基础sql语法)
增:
为实现图书信息的增加,我们需要先定义一个书籍类(包含图书的各项信息)
class Book
{
int number,price,quantity;
String id,name,author,publisher,isbn;
String date;
}
以下是方法函数
注意:自己数据库不同的话,对应的sql语句需要修改
以下代码PreparedStatement pstmt = con.prepareStatement(sql)
中的con是来源于连接数据库代码的变量con
void Insert(Book book) throws SQLException
{
String sql = "insert into Bookinfo"
+ "(number,Book_Id,Book_Name,Book_Author,Book_Publisher,Book_Price,Book_data,Book_ISBN,Book_Quantity)"
+ " values(?,?,?,?,?,?,?,?,?)";
`PreparedStatement pstmt = con.prepareStatement(sql)`;
pstmt.setInt(1, book.number);
pstmt.setString(2, book.id);
pstmt.setString(3, book.name);
pstmt.setString(4, book.author);
pstmt.setString(5, book.publisher);
pstmt.setInt(6, book.price);
pstmt.setString(7, book.date);
pstmt.setString(8, book.isbn);
pstmt.setInt(9, book.quantity);
pstmt.executeUpdate();
pstmt.close();
//con.close();
}
删
我的思路是按书的id将其从表单中删去
(也可以自己做修改)
void Delete(String id) throws SQLException
{
String sql = "delete Bookinfo where Book_Id = ? ";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, id);
pstmt.executeUpdate();
pstmt.close();
//con.close();
}
查
查找则是按书名查找,并打印书籍的简单信息
void Serch(String name) throws SQLException
{
String sql = "SELECT * FROM Bookinfo WHERE Book_Name = ? ";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, name);
ResultSet rs = pstmt.executeQuery();
while(rs.next())
{
System.out.println(rs.getString("Book_Name")+" "
+rs.getString("Book_Author")+" "
+rs.getInt("Book_Price")+"$ "
+rs.getInt("Book_Quantity"));
}
pstmt.close();
//con.close();
}
改的操作太过麻烦(可以改的选项有很多种)就没有实现,读者可以自己参考资料写出。
以下是完整代码:
以下代码运行二次报错的原因是因为,重复插入了相同id的书,去掉相应代码就行。
笔者能力有限,涉及重复插入,插入值为空的等问题均未写有关的错误机制。
如果有关读者做适当修改时运行报错,可能是以上原因,也有可能是sql语句错误的问题。
供大家参考学习就好~
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.spi.DirStateFactory.Result;
class Book
{
int number,price,quantity;
String id,name,author,publisher,isbn;
String date;
}
class sql_connection
{
Connection con;
sql_connection()
{
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String url = "jdbc:sqlserver://127.0.0.1:1433;databaseName=My_DataBase";
con = DriverManager.getConnection(url,"sa","123456");
// sa是用户名,12345是数据库密码
System.out.println("数据库连接成功");
//con.close(); // 关闭数据库访问
}
catch(Exception e) {
System.out.println("数据库连接失败\n" + e.toString());
}
this.con = con;
}
void Insert(Book book) throws SQLException
{
String sql = "insert into Bookinfo"
+ "(number,Book_Id,Book_Name,Book_Author,Book_Publisher,Book_Price,Book_data,Book_ISBN,Book_Quantity)"
+ " values(?,?,?,?,?,?,?,?,?)";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setInt(1, book.number);
pstmt.setString(2, book.id);
pstmt.setString(3, book.name);
pstmt.setString(4, book.author);
pstmt.setString(5, book.publisher);
pstmt.setInt(6, book.price);
pstmt.setString(7, book.date);
pstmt.setString(8, book.isbn);
pstmt.setInt(9, book.quantity);
pstmt.executeUpdate();
pstmt.close();
//con.close();
}
void Delete(String id) throws SQLException
{
String sql = "delete Bookinfo where Book_Id = ? ";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, id);
pstmt.executeUpdate();
pstmt.close();
//con.close();
}
void Serch(String name) throws SQLException
{
String sql = "SELECT * FROM Bookinfo WHERE Book_Name = ? ";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, name);
ResultSet rs = pstmt.executeQuery();
while(rs.next())
{
System.out.println(rs.getString("Book_Name")+" "
+rs.getString("Book_Author")+" "
+rs.getInt("Book_Price")+"$ "
+rs.getInt("Book_Quantity"));
}
pstmt.close();
//con.close();
}
void Print_List(int len) throws SQLException
{
int i=0;
String sql = "select * from Bookinfo";
PreparedStatement pstmt = con.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while(rs.next()&&i<len)
{
System.out.println(rs.getString("Book_Name")+" "
+rs.getString("Book_Author")+" "
+rs.getInt("Book_Price")+"$ "
+rs.getInt("Book_Quantity"));
i++;
}
//pstmt.executeUpdate();
pstmt.close();
//con.close();
}
void close() throws SQLException
{
this.con.close();
}
}
public class work_421 {
public static void main(String[] args) throws SQLException {
sql_connection a = new sql_connection();
//设置添加的书的信息
Book book = new Book();
book.number=11;
book.id="00011";
book.author="罗贯中";
book.date="2000-1-1";
book.name="三国演义";
book.isbn="xxx11";
book.publisher="人民出版社";
book.quantity=1;
book.price=999;
//打印数据库的信息 参数为打印数量
System.out.println("------------------------------------");
a.Print_List(999);
//检索书名为c++的书的信息(检索按书籍名称检索)
System.out.println("------------------------------------");
a.Serch("C++");
//插入书的信息
System.out.println("------------------------------------");
a.Insert(book);
a.Print_List(999);
//删除之前插入的数据(删除是按书籍id删除)
System.out.println("------------------------------------");
a.Delete("00011");
a.Print_List(999);
//
}
}
测试一下: