JDBC回顾

JDBC：对数据库进行增删改查

导jar包新建一个lib文件，把jar包复制进去  Project Settings里Libraries+选择lib里的jar文件

数据库中有一张表，对应Java就要有一个实体类  entity包

1）测试一下是否能连上数据库，必须要加jar包

public class DBconnection {
    private static final String URL="jdbc:mysql://localhost:3306/java_book?Unicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC";
    private static final String USER_NAME="root";
    private static final String USER_PWD="591670";

    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");//8.0要加cj
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }//DriverManager数据库的驱动器
    public static Connection getConnection() throws SQLException {
        Connection connection= DriverManager.getConnection(URL,USER_NAME,USER_PWD);
        return connection;
    }
    public static void closeConnection(Connection connection) throws SQLException {
        if(connection!=null){
            connection.close();
        }
    }
}
 
//        try {
//            Connection connection= DBconnection.getConnection();
//            System.out.println(connection);
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }



数据库里的id主键设置为自增长

Controller:控制层，和用户的需求做对接

Service:业务逻辑层，处理业务逻辑，并获取对象

dao:真正执行SQL语句，面向数据库

Statement缺点，拼写繁杂，不安全，SQL注入的漏洞

select count（*）from book where title=‘xxxx’ or 1=1;是恒真

package com.iss.dao;

import com.iss.entity.Book;
import com.mysql.cj.xdevapi.Result;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

// 更新 String sql="update book set title=?,author=?,price=?where id=?";
// 删除 String sql="delete from book where id=?"
//set 问号占位符
public class BookDaoImpl implements IBookDao{

    private Connection connection;
    public BookDaoImpl(Connection connection){
        this.connection=connection;
    }
    @Override
    public int addBook(Book book) throws SQLException {
//        String sql="insert into book values(null,'"+book.getTitle()+"','"+book.getAuthor()+"',"+book.getPrice()+")";
        String sql="insert into book values(null ,?,?,?)";//问号占位符
//        System.out.println(sql);
     //   Stamteent statement=connection.createStatement();
        PreparedStatement pstm=connection.prepareStatement(sql);
        pstm.setString(1,book.getTitle());//参数索引
        pstm.setString(2,book.getAuthor());//给问号占位符赋值
        pstm.setDouble(3,book.getPrice());
        int result=pstm.executeUpdate();//得到int 适合增删改
        // 不需要加sql,不用管数据类型，先把SQL语句传走，再把占位符的值传走，自动组装
        pstm.close();
        System.out.println(result);
        return result;
    }

    @Override
    public List<Book> queryAllBooks() throws SQLException {
        List<Book> list=new ArrayList<>();
        String sql="select*from book";//select * from book where title=?";  再给title赋值
        PreparedStatement pstm=connection.prepareStatement(sql);//预处理SQL
        ResultSet rs =pstm.executeQuery();//得到ResultSet结果集，游标
        while (rs.next()){
            Book book=new Book();
            book.setId(rs.getInt(1));//id
            book.setTitle(rs.getString(2));//按照select查询走
            book.setAuthor(rs.getString(3));
            book.setPrice(rs.getDouble(4));
            list.add(book);
        }
        rs.close();
        pstm.close();
        return list;
    }
}
 
package com.iss.dao;

import com.iss.entity.Book;

import java.sql.SQLException;
import java.util.List;

public interface IBookDao {
    int addBook(Book book) throws SQLException;
    List<Book> queryAllBooks() throws SQLException;
}

 

package com.iss.service;

import com.iss.conn.DBconnection;
import com.iss.dao.BookDaoImpl;
import com.iss.entity.Book;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class BookService {
    //获取连接
//    private DBconnection dbconnection;
//    private Connection connection;
    private BookDaoImpl dao;
    public BookService() throws SQLException {
       dao=new BookDaoImpl(DBconnection.getConnection());
    }
    public int addBook(Book book) throws SQLException {
          return dao.addBook(book);
    }
    public List<Book> queryAllBooks() throws SQLException{
        return dao.queryAllBooks();
    }

}


package com.iss.demo;

import com.iss.conn.DBconnection;
import com.iss.controller.BookController;

import java.sql.Connection;
import java.sql.SQLException;

public class demo {
    public static void main(String[] args) {
//        try {
//            Connection connection= DBconnection.getConnection();
//            System.out.println(connection);
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
        new BookController();
    }
}
 
package com.iss.controller;

import com.iss.entity.Book;
import com.iss.service.BookService;

import java.sql.SQLException;
import java.util.List;

public class BookController {
    private BookService service;
    public BookController(){
        //添加图书
        try {
            service=new BookService();
        } catch (SQLException e) {
            e.printStackTrace();
        }
//        addBook();
        queryAllBooks();

    }
    public void addBook(){
        Book book=new Book();
        book.setTitle("西游记");
        book.setAuthor("吴承恩");
        book.setPrice(8.8);
        try {
           int result= service.addBook(book);
            System.out.println(result+"条插入成功");
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
    public void queryAllBooks(){
        try {
            List<Book>list=service.queryAllBooks();
            for (Book book:list){
                System.out.println(book);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}
 
package com.iss.entity;

import java.io.Serializable;

public class Book implements Serializable {
    private Integer id;
    private String title;
    private String author;
    private double price;

    public Book() {
    }

    public Book(Integer id, String title, String author, double price) {
        this.id = id;
        this.title = title;
        this.author = author;
        this.price = price;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }

    @Override
    public String toString() {
        return "Book{" +
                "id=" + id +
                ", title='" + title + '\'' +
                ", author='" + author + '\'' +
                ", price=" + price +
                '}';
    }
}
 






 
 
 
 










            

            
posted @ 
2021-03-12 17:28 
YangYuJia 
阅读(56) 
评论(0) 
 
收藏 
举报