数据库学的特别渣,期末考试都是蒙过的,哈哈
现在正好可以好好的复(yu)习一下,数据库知识特别的重要,任何程序,网页不连数据库,都是废品一个
数据库的知识必须学好,啰嗦了这么多,来咱们一起回顾一下吧
1.首先安装mysql,去官网下载就可以
2.导包,将mysql的驱动包复制到WebContent/WEB-INF/lib目录下
3.开始写代码
(1).加载jdbc驱动类
Class.forName(com.mysql.jdbc.Driver);
(2).建立数据库连接
Connnection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/数据库名"
"+"?user=root&password = root&useUnicode=true&characterEncoding=utf-8"));
(3).创建PreparedStatement对象
String sql="select * from books";
PreparedStatement pst =conn.preparedStatement(sql);
以下是查询操作,其实增删改查都一样,只是sql语句不同
ResultSet rs=pst.executeQuery();
while(rs.next()){
System.out.println(rs.getString("isbn") + " "
+ rs.getString("bookName") + " "
+ rs.getInt("publisherId") + " "
+ rs.getDouble("price") + " " + rs.getInt("count"));
}
//关闭
rs.close();
pst.close();
conn.close();
好了,应该连接好了
package ch_01;
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 java.util.Scanner;
public class test1{
Scanner sc=new Scanner(System.in);
/**
* 图书管理界面
*
* @param sc
*/
private void View() {
int id;
System.out.println("\t\t\t------------------欢迎来到图书管理界面------------------");
System.out.println("\t\t\t------------------1、查看图书信息------------------");
System.out.println("\t\t\t------------------2、添加图书信息------------------");
System.out.println("\t\t\t------------------3、修改图书信息------------------");
System.out.println("\t\t\t------------------4、删除图书信息------------------");
System.out.println("\t\t\t------------------5、退出------------------");
System.out.print("请输入要进行的操作:");
String operate=sc.next();
switch(operate){
case "1":
//调用UserServiceImpl类中的searchUser(sc)查询用户信息;
searchBook();
//查询完毕后再返回用户管理界面,重新选择其他操作,递归调用showUserView(sc)方法。
View();
break;
case "2":
//调用UserServiceImpl类中的addUser(sc)添加用户信息;
addBook();
//查询完毕后再返回用户管理界面,重新选择其他操作,递归调用showUserView(sc)方法。
View();
break;
case "3":
System.out.println("请输入要修改的图书编号:");
Scanner sd = new Scanner(System.in);
updateBook(sd.next());
View();
break;
case "4":
System.out.println("请输入要删除的图书编号:");
Scanner s2d = new Scanner(System.in);
deleteBook(s2d.next());
View();
break;
case "5":
//返回到调用它的上一级showAdminView(sc)方法
System.out.println("您已退出系统!");
System.exit(0);
break;
default:
System.out.println("输入的操作符不正确,请重新输入:");
View();
}
}
private void deleteBook(String isbn) {
try {
//加载jdbc驱动类
Class.forName("com.mysql.jdbc.Driver");
//建立数据库连接
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/students"
+"?user=root&password=root&useUnicode=true"+
"&characterEncoding=utf-8");
//创建Statement/PreparedStatement对象
// String sql="delete * from books where isbn=?";
// PreparedStatement pst=conn.prepareStatement(sql);
// pst.setInt(1,id);
String sql="delete from books where isbn=?";
PreparedStatement pst = conn.prepareStatement(sql);
//设置动态参数
pst.setString(1,isbn);
int x=pst.executeUpdate();
//处理结果
if(x>0){
System.out.println("成功删除!");
}
else{
System.out.println("删除失败! ");
}
//关闭
pst.close();
conn.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private void updateBook(String isbn) {
try {
//加载jdbc驱动类
Class.forName("com.mysql.jdbc.Driver");
//建立数据库连接
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/students"
+"?user=root&password=root&useUnicode=true"+
"&characterEncoding=utf-8");
//实现更新
String sql="update books set bookname=?,publisherid=?,price=?,count=? "
+ "where isbn=?";
//创建Statement/PreparedStatement对象
PreparedStatement pst = conn.prepareStatement(sql);
//输入修改后的值
Scanner s=new Scanner(System.in);
System.out.println("请输入bookname:");
String bookname=s.next();
System.out.println("请输入publisherid:");
int publisherid=s.nextInt();
System.out.println("请输入price:");
Double price=s.nextDouble();
System.out.println("请输入count:");
int count=s.nextInt();
//设置动态参数
pst.setString(1,bookname);
pst.setInt(2,publisherid);
pst.setDouble(3, price);
pst.setInt(4,count);
pst.setString(5,isbn);
// pst.setString(1, "CCC");
// pst.setInt(2,123123);
// pst.setDouble(3,23.4);
// pst.setInt(4, 23);
// pst.setString(5,isbn);
int x=pst.executeUpdate();
//处理结果
if(x>0){
System.out.println("成功修改!");
}
else{
System.out.println("修改失败! ");
}
//关闭
pst.close();
conn.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private void addBook() {
try {
//加载jdbc驱动类
Class.forName("com.mysql.jdbc.Driver");
//建立数据库连接
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/students"
+"?user=root&password=root&useUnicode=true"+
"&characterEncoding=utf-8");
//创建Statement/PreparedStatement对象
// String sql="insert into userdetail(username) values=(?)";
String sql="insert into books(isbn,bookname,publisherid,price,"
+ "count) values(?,?,?,?,?)";
PreparedStatement pst=conn.prepareStatement(sql);
Scanner s=new Scanner(System.in);
System.out.println("请输入isbn:");
String isbn=s.next();
System.out.println("请输入bookname:");
String bookname=s.next();
System.out.println("请输入publisherid:");
int publisherid=s.nextInt();
System.out.println("请输入price:");
Double price=s.nextDouble();
System.out.println("请输入count:");
int count=s.nextInt();
//设置动态参数
pst.setString(1,isbn);
pst.setString(2,bookname);
pst.setInt(3,publisherid);
pst.setDouble(4, price);
pst.setInt(5,count);
//pst.setString(1,"a1452dmin");
int x=pst.executeUpdate();
//处理结果
if(x>0){
System.out.println("成功插入!");
}
else{
System.out.println("插入失败! ");
}
pst.close();
conn.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private void searchBook() {
try {
//加载jdbc驱动类
Class.forName("com.mysql.jdbc.Driver");
//建立数据库连接
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/students"
+"?user=root&password=root&useUnicode=true"+
"&characterEncoding=utf-8");
//创建PreparedStatement对象
String sql="select * from books";
PreparedStatement pst=conn.prepareStatement(sql);
ResultSet rs=pst.executeQuery();
while(rs.next()){
System.out.println(rs.getString("isbn") + " "
+ rs.getString("bookName") + " "
+ rs.getInt("publisherId") + " "
+ rs.getDouble("price") + " " + rs.getInt("count"));
}
//关闭
rs.close();
pst.close();
conn.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private boolean searchBookById(int id){
boolean flag=false;
return flag;
}
public static void main(String[] args) {
new test1().View();
}
}