创建MYSQL数据库新建查询
写入MYSQl代码
#设置数据的视图---使用数据库
use mydb;
#判断表存在就删除表
drop table if exists student;
#创建表
create table student
(stuId int primary key auto_increment,
stuName varchar(20),
stuSex varchar(2),
stuAge int,
stuAddr varchar(50))
#插入测试数据
insert into student(stuName,stuSex,stuAge,stuAddr) values('张三','男',20,'河南');
insert into student(stuName,stuSex,stuAge,stuAddr) values('小美','女',18,'山东');
insert into student(stuName,stuSex,stuAge,stuAddr) values('Rose','女',19,'美国');
insert into student(stuName,stuSex,stuAge,stuAddr) values('Jack','男',21,'英国');
#查询数据表
select * from student;
打开idea创建项目导入jar包
最好是与mysql版本一致
当出现为成功
链接数据库
首先确定数据库打开状态
其次配置链接参数
databaseName 指定的数据库名字
UTC现在的时间
MySQL 5.0版本:
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/databaseName?useSSL=false&serverTimezone=UTC
username=root
password=123
MySQL 8.0版本:
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/databaseName?useSSL=false&serverTimezone=UTC
username=root
password=123
创建数据库对象
代码
package com.hp.bean;
public class Book {
//属性
private int bookId;
private String bookName;
private String bookAuthor;
private int bookNum;
private double bookPrice;
private String introduce;
public Book() {
}
//构造方法
public Book(int bookId, String bookName, String bookAuthor, int bookNum, double bookPrice, String introduce) {
this.bookId = bookId;
this.bookName = bookName;
this.bookAuthor = bookAuthor;
this.bookNum = bookNum;
this.bookPrice = bookPrice;
this.introduce = introduce;
}
//getter和setter方法
public int getBookId() {
return bookId;
}
public void setBookId(int bookId) {
this.bookId = bookId;
}
public String getBookName() {
return bookName;
}
public void setBookName(String bookName) {
this.bookName = bookName;
}
public String getBookAuthor() {
return bookAuthor;
}
public void setBookAuthor(String bookAuthor) {
this.bookAuthor = bookAuthor;
}
public int getBookNum() {
return bookNum;
}
public double getBookPrice() {
return bookPrice;
}
public void setBookPrice(double bookPrice) {
this.bookPrice = bookPrice;
}
public String getIntroduce() {
return introduce;
}
public void setIntroduce(String introduce) {
this.introduce = introduce;
}
//tostring方法
@Override
public String toString() {
return "Book{" +
"bookId=" + bookId +
", bookName='" + bookName + '\'' +
", bookAuthor='" + bookAuthor + '\'' +
", bookNum=" + bookNum +
", bookPrice=" + bookPrice +
", introduce='" + introduce + '\'' +
'}';
}
//重写setter方法
public void setBookNum(int bookNum){
if(bookNum>0&&bookNum<100){
this.bookNum = bookNum;
}
}
}
创建测试程序
注意要单独创建一个包存储测试类
全查
/**
* 全查
*/
@Test
public void testshowAll() throws ClassNotFoundException, SQLException {
//使用反射链接数据库
Class.forName(driver);
Connection con= DriverManager.getConnection(url,username,password);
//创建sql数据库
String sql="select * from books";
//执行sql语句
PreparedStatement pr=con.prepareStatement(sql);
//数据库响应的查询结果存放在ResultSet
ResultSet rs=pr.executeQuery();
//创建list面向接口
List<Book> list=new ArrayList<>();
//循环遍历数据 next下一行(第一行不执行)
while (rs.next()){
//创建新的空间来进行存值
Book book=new Book();
//get:根据id获取 set:存入id获取的值
book.setBookId(rs.getInt("bookId"));
book.setBookName(rs.getString("bookName"));
book.setBookAuthor(rs.getString("bookAuthor"));
book.setBookNum(rs.getInt("bookNum"));
book.setBookPrice(rs.getDouble("bookPrice"));
book.setIntroduce(rs.getString("introduce"));
//list.add在list接口中存值
list.add(book);
}
//输出list接口
System.out.println(list);
//7.资源的释放
if(rs!=null){
rs.close();
}
if(pr!=null){
pr.close();
}
if(con!=null){
con.close();
}
}
根据书籍查询信息
/**
* 根据书籍名称查询信息*/
public Book find(String bookName) throws ClassNotFoundException, SQLException {
//定义一个空值如果无值则返回空值 有值则输出目标值
Book book = null;
//使用反射链接数据库
Class.forName(driver);
Connection con = DriverManager.getConnection(url, username, password);
//编写条件查询语句
String sql = "select * from books where bookName=?";
//执行sql语句
PreparedStatement pr = con.prepareStatement(sql);
//第一个问号的值
pr.setObject(1, bookName);
//数据库响应的查询结果存放在ResultSet
ResultSet rs = pr.executeQuery();
//rs.next() 第一组数据不执行
if (rs.next()) {
book = new Book();
//set 存值 get获取值
book.setBookId(rs.getInt("bookId"));
book.setBookName(rs.getString("bookName"));
book.setBookAuthor(rs.getString("bookAuthor"));
book.setBookNum(rs.getInt("bookNum"));
book.setBookPrice(rs.getDouble("bookPrice"));
book.setIntroduce(rs.getString("introduce"));
}
//7.资源的释放
if (rs != null) {
rs.close();
}
if (pr != null) {
pr.close();
}
if (con != null) {
con.close();
}
//返回结果集
return book;
}
@Test
public void testFind() throws SQLException, ClassNotFoundException {
//创建Scanner控制台
Scanner sc=new Scanner(System.in);
//提示用户输入
System.out.println("(全名称)请输入你需要查询的书名");
//获取用户输入的值并返回结果 正确则输出 无结果则返回null
Book num=find(sc.next());
//执行判断语句
if (num==null){
//结果为null 则输出
System.out.println("查询不到此书名");
}else {
//结果正确 则输出
System.out.println(num);
}
}
模糊查询
/**
* 模糊查询
*/
public List<Book> FindKwyword(String keyword) throws ClassNotFoundException, SQLException {
Class.forName(driver);
Connection con = DriverManager.getConnection(url, username, password);
String sql = "select * from books where bookName like ?";
PreparedStatement psm = con.prepareStatement(sql);
psm.setObject(1, "%" + keyword + "%");
ResultSet rs = psm.executeQuery();
List<Book> booksList = new ArrayList<>();
while (rs.next()) {
Book books = new Book();
books.setBookId(rs.getInt("bookId"));
books.setBookName(rs.getString("bookName"));
books.setBookAuthor(rs.getString("bookAuthor"));
books.setBookNum(rs.getInt("bookNum"));
books.setBookPrice(rs.getInt("bookPrice"));
books.setIntroduce(rs.getString("introduce"));
booksList.add(books);
}
System.out.println(booksList);
if (rs != null) {
rs.close();
}
if (psm != null) {
psm.close();
}
if (con != null) {
con.close();
}
return booksList;
}
@Test
public void testFindKwyword() throws SQLException, ClassNotFoundException {
Scanner sc=new Scanner(System.in);
System.out.println("请输入你需要模糊查询的名字(模糊查询)");
FindKwyword(sc.next());
}
删除
/**
* 删除
*/
public boolean delete(int bookId) throws ClassNotFoundException, SQLException {
Class.forName(driver);
Connection con = DriverManager.getConnection(url, username, password);
String sql = "delete from books where bookId =?";
PreparedStatement psm = con.prepareStatement(sql);
psm.setObject(1, bookId);
int n = psm.executeUpdate();
if (n > 0) {
System.out.println("删除成功");
} else {
System.out.println("删除失败");
}
if (psm != null) {
psm.close();
}
if (con != null) {
con.close();
}
return false;
}
@Test
public void testDelect() throws SQLException, ClassNotFoundException {
Scanner sc=new Scanner(System.in);
System.out.println("请输入你需要删除的ID(删除)");
delete(sc.nextInt());
};
添加
/**
* 添加
*/
public boolean add(Book book) throws ClassNotFoundException, SQLException {
Class.forName(driver);
Connection con = DriverManager.getConnection(url, username, password);
String sql = "insert into books(bookName,bookAuthor,bookNum,bookPrice,introduce) values(?,?,?,?,?)";
PreparedStatement psm = con.prepareStatement(sql);
psm.setObject(1, book.getBookName());
psm.setObject(2, book.getBookAuthor());
psm.setObject(3, book.getBookNum());
psm.setObject(4, book.getBookPrice());
psm.setObject(5, book.getIntroduce());
int n = psm.executeUpdate();
if(n>0){
System.out.println("插入数据成功");
}else{
System.out.println("插入数据失败");
}
//7释放资源
if(psm!=null){
psm.close();
}
if(con!=null){
con.close(); }
return false;
}
@Test
public void testadd() throws SQLException, ClassNotFoundException {
Book book=new Book();
Scanner sc=new Scanner(System.in);
System.out.println("请输入书名(添加)");
book.setBookName(sc.next());
System.out.println("请输入作者");
book.setBookAuthor(sc.next());
System.out.println("请输入数量");
book.setBookNum(sc.nextInt());
System.out.println("请输入价格");
book.setBookPrice(sc.nextDouble());
System.out.println("请输入图书简介");
book.setIntroduce(sc.next());
add(book);
}
修改
/**
* 修改
*/
public Boolean changePrice(String bookName,double bookprice) throws ClassNotFoundException, SQLException {
//使用反射链接数据库
Class.forName(driver);
Connection con=DriverManager.getConnection(url,username,password);
//编写修改语句
String sql="update books set bookprice=? where bookName=?";
//执行sql语句
PreparedStatement pr=con.prepareStatement(sql);
pr.setObject(1,bookprice);
pr.setObject(2,bookName);
int n = pr.executeUpdate();
if (n > 0) {
System.out.println("修改成功");
} else {
System.out.println("修改失败");
}
return false;
}
@Test
public void testChangePrice() throws SQLException, ClassNotFoundException {
Scanner sc=new Scanner(System.in);
System.out.println("请输入图书名称");
String name=sc.next();
Book book=find(name);
System.out.println("修改前"+book);
System.out.println("请输入修改的图书价格");
Double price =sc.nextDouble();
changePrice(name, price);
System.out.println("修改后"+find(name));
};