作者：starry-night--_848 | 来源：互联网 | 2018-07-10 08:41
通过一个综合型的例子加深对JDBC操作数据库的增、删、改、查的运用。经典的图书信息录入实例设计数据库CREATETABLE`tb_books`(`id`int(10)unsignedNOTNULLAUTO_I
通过一个综合型的例子加深对JDBC操作数据库的增、删、改、查的运用。经典的图书信息录入实例设计数据库CREATETABLE`tb_books`(`id`int(10)unsignedNOTNULLAUTO_I
通过一个综合型的例子加深对JDBC操作数据库的增、删、改、查的运用。
经典的图书信息录入实例
设计数据库
CREATE TABLE `tb_books`
(
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
`price` double NOT NULL,
`bookCount` int(10) unsigned NOT NULL,
`author` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
)
写一个Book类对图书信息进行封装
package com.lixiyu;
public class Book {
private int id;
private String name;
private double price;
private int bookCount;
private String author;
public int getId(){
return id;
}
public void setId(int id){
this.id=id;
}
public String getName(){
return name;
}
public void setName(String name){
this.name=name;
}
public double getPrice(){
return price;
}
public void setPrice(double price){
this.price=price;
}
public int getbookCount(){
return bookCount;
}
public void setbookCount(int bookCount){
this.bookCount=bookCount;
}
public String getAuthor(){
return author;
}
public void setAuthor(String author){
this.author=author;
}
}
添加（insert）图书信息操作
创建AddBook.jsp页面，用于对添加图书信息进行处理
<%@ page language="java" cOntentType="text/html; charset=GB18030"
pageEncoding="GB18030"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.PreparedStatement"%>
<%request.setCharacterEncoding("UTF-8"); %>
<%
try{
Class.forName("com.mysql.jdbc.Driver");//加载数据库驱动，注册到驱动管理器
String url="jdbc:mysql://localhost:3306/db_test";//数据库连接字符串
String username="root";//数据库用户名
String password="lixiyu";//数据库密码
Connection cOnn=DriverManager.getConnection(url,username,password);//创建Connection连接
String sql="insert into tb_books(name,price,bookCount,author)values(?,?,?,?)";//添加图书信息sql语句
PreparedStatement ps=conn.prepareStatement(sql);//获取PreparedStatement
ps.setString(1,book.getName());//对SQL语句中的第1个参数赋值
ps.setDouble(2,book.getPrice());
ps.setInt(3,book.getbookCount());
ps.setString(4,book.getAuthor());//对SQL语句中的第4个参数赋值
int row=ps.executeUpdate();//执行更新操作，返回所影响的行数
if(row>0){
out.print("成功添加了"+row+"条数据");
}
ps.close();
conn.close();
}catch(Exception e){
out.print("图书信息添加失败！");
e.printStackTrace();
}
%>
创建insert.jsp，用于创建添加图书信息所需的表单
<%@ page language="java" cOntentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
最后运行
查询（select）图书信息操作
创建FindServlet的servlet对象用于查询所有图书的信息。编写doGet()方法，建立数据库连接，并将所有查询数据集合放置HttpServletRequest对象中，将请求转发到jsp页面中：
package com.lixiyu;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* Servlet implementation class FindServlet
*/
public class FindServlet extends HttpServlet {
private static final long serialVersiOnUID= 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public FindServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
try{
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/db_test";
String username="root";
String password="lixiyu";
Connection cOnn=DriverManager.getConnection(url,username,password);
Statement stmt=conn.createStatement();//获取statement对象
String sql="select * from tb_books";
ResultSet rs=stmt.executeQuery(sql);
List
list=new ArrayList();//实例化list对象
while(rs.next()){
Book book=new Book();
book.setId(rs.getInt("id"));//对id属性赋值
book.setName(rs.getString("name"));
book.setPrice(rs.getDouble("price"));
book.setbookCount(rs.getInt("bookCount"));
book.setAuthor(rs.getString("author"));
list.add(book);//将图书对象添加到集合中
}
request.setAttribute("list", list);//将图书集合放置到request中
rs.close();//关闭ResultSet
stmt.close();//关闭Statement
conn.close();//关闭Connection
}catch(ClassNotFoundException e){
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}
request.getRequestDispatcher("book_list.jsp").forward(request, response);//请求转发到book_List.jsp
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
}
}
京公网安备 11010802041100号