配置idea將Java與數(shù)據(jù)庫連接起來實現(xiàn)一個簡單的圖書管理系統(tǒng)
1.通過connector連接Java和Mysql數(shù)據(jù)庫
(1)首先配置idea
我們此處用的數(shù)據(jù)庫是Mysql8.0版本,注意8.0版本的connector要用8.0,用5.0的后面會報錯,下載網(wǎng)址:MySQL :: Download Connector/J 下載完成后點擊加號,配置到idea中,配置完成后,idea的externLibrary中會有新添加的類包
我們的初步任務就完成啦!
(2)如何把java和mysql連接起來
1.簡單連接
String url = "jdbc:mysql://localhost:3306/jdbc?useSSL=false"; String user = "root"; String password = "18342003" ; try { Class.forName("com.mysql.cj.jdbc.Driver"); con = DriverManager.getConnection(url,user, password); }catch(SQLEXCEPTION ex){ ex.printStackTrace(); }finally{ try { con.close(); st.close(); rs.close(); }catch(Exception ex){ ex.printStackTrace(); } }
通過這樣的代碼我們可以實現(xiàn)簡單的java與mysql的連接,需要注意url = jdbc:mysql://主機名:域名/數(shù)據(jù)庫名稱 user = 用戶名 password = 密碼 有mysql基礎的同學應該很容易理解,finally我們關閉連接。這樣實現(xiàn)了簡單的連接
連接后我們可以寫幾個sql語句檢驗是
try { Class.forName("com.mysql.cj.jdbc.Driver"); con = DriverManager.getConnection(url,user, password); String sql_insert = "insert into student values(10,'aa',30)"; String sql_insert1 = "insert into student values(11,'aa',30)"; String sql_insert2 = "insert into student values(12,'aa',30)"; String sql_select = "select * from student"; //執(zhí)行sql語句 st.executeUpdate(sql_insert); st.executeUpdate(sql_insert1); st.executeUpdate(sql_insert2); while(rs.next()){ System.out.println(rs.getInt(1)+","+ rs.getString(2)+","+ rs.getInt(3) ); } }catch(Exception ex){ ex.printStackTrace(); }finally{ try { con.close(); st.close(); rs.close(); }catch(Exception ex){ ex.printStackTrace(); }
2.認識PrepareStatement
但是具體在添加數(shù)據(jù)庫中,我們書寫sql語句不可能是死的,肯定變化的,所以我們有著PrepareStatement用來寫變化的sql語句 具體內(nèi)容如下
PreparedStatement preparedStatement = con.prepareStatement("insert into student values(?,?,?)"); preparedStatement.setInt(1,3); preparedStatement.setString(2,"abc"); preparedStatement.setInt(3,20); preparedStatement.executeUpdate();
2.實現(xiàn)簡單的圖書管理系統(tǒng)
(1)創(chuàng)建數(shù)據(jù)庫jdbc,并且創(chuàng)建出book表
可以在cmd命令行中輸入create database jdbc;,也可以在navicat中直接創(chuàng)建
(2)在idea中書寫代碼將mysql與java連接,實現(xiàn)基本的增刪改查
1.JdbcUtiles類,一個使用jdbc的工具類
package myDatebases_Demo; import java.util.*; import java.sql.*; public class JdbcUtil { private static final String DRIVER = "com.mysql.cj.jdbc.Driver"; private static final String URL = "jdbc:mysql://localhost:3306/jdbc?useSSL=false"; private static final String USER = "root"; private static final String PASSWORD = "18342003"; //注冊驅(qū)動程序放在代碼塊中,每次只能注冊一次 static { try { Class.forName(DRIVER); } catch (ClassNotFoundException e) { e.printStackTrace(); } } //創(chuàng)建getConnection對象,用來獲得connection對象 public static Connection getConnection(){ Connection conn = null; try { conn = DriverManager.getConnection(URL,USER,PASSWORD); }catch (SQLException e){ e.printStackTrace(); } return conn; } //創(chuàng)建free方法實現(xiàn)關閉連接功能 public static void close(Statement st, Connection conn) { if (st != null) { try { st.close(); } catch (SQLException e) { e.printStackTrace(); } finally { if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } } public static void close(ResultSet rs, Statement st, Connection conn) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } finally { if (st != null) { try { st.close(); } catch (SQLException e) { e.printStackTrace(); } finally { if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } } } } }
這個類在任何系統(tǒng)中都可以使用,是一個泛用性的,可以直接復制粘貼,畢竟java是一個面向復制的編程語言
2.BookDb類,實現(xiàn)增刪改查
1.增
public static void addBook(Book s) { try { Connection conn = JdbcUtil.getConnection(); PreparedStatement pst = conn.prepareStatement("insert into book values (?,?,?)"); pst.setInt(1, s.getId()); pst.setString(2, s.getName()); pst.setString(3, s.getPress()); pst.executeUpdate(); JdbcUtil.close(pst, conn); } catch (SQLException ex) { ex.printStackTrace(); } }
2.刪
//根據(jù)書本id刪除書本信息 public static void delectBook(int id){ try { Connection conn = JdbcUtil.getConnection(); PreparedStatement pst = conn.prepareStatement("delete from book where id = ?"); pst.setString(1, String.valueOf(id)); pst.executeUpdate(); JdbcUtil.close(pst, conn); } catch (SQLException ex) { ex.printStackTrace(); } }
3.改
//根據(jù)書本id更新書本信息 public static void updateBook(Book b, int id) { try { Connection conn = JdbcUtil.getConnection(); PreparedStatement pst = conn.prepareStatement("update book set id = ?,name = ?,press = ? where id = ? "); pst.setInt(1, b.getId()); pst.setString(2, b.getName()); pst.setString(3, b.getPress()); pst.executeUpdate(); JdbcUtil.close(pst, conn); } catch (SQLException ex) { ex.printStackTrace(); } }
4.查
//根據(jù)書本id查詢書本的信息 //保存在一個對象中,用于集中輸出,或者放在集合中,用于管理輸出等 public static Book findBookById(int id) { Book b = new Book(); try { Connection conn = JdbcUtil.getConnection(); PreparedStatement pst = conn.prepareStatement("select * from book where id = ?"); pst.setInt(1, id); ResultSet rs = pst.executeQuery(); if (rs.next()) { b.setId(rs.getInt(1)); b.setName(rs.getString(2)); b.setPress(rs.getString(3)); } JdbcUtil.close(rs, pst, conn); } catch (SQLException ex) { ex.printStackTrace(); } return b; }
將信息都放在一個對象容器中,輸出時用對象容器進行輸出,因為我們具體在mysql中查詢一個表的信息時,表的每一類是一個字段,而每一行正好可以對應java中的一個對象,所以我們可以把查到的所有信息都放在一個對象中,代表我們表的每一行
5.顯示所有信息
//查詢書本的所有信息,保存在一個集合中,后來再通過集合再把所有結(jié)果輸出出來,避免此中語句過于冗雜,利于以后的更改 public static ArrayList<Book> queryBook() { ArrayList<Book> list = new ArrayList<>(); try { Connection conn = JdbcUtil.getConnection(); PreparedStatement pst = conn.prepareStatement("select * from book"); ResultSet rs = pst.executeQuery(); while (rs.next()) { Book b = new Book(); b.setId(rs.getInt(1)); b.setName(rs.getString(2)); b.setPress(rs.getString(3)); list.add(b); } JdbcUtil.close(rs,pst,conn); } catch (SQLException ex) { ex.printStackTrace(); } return list; }
此處便更好理解了,將所得到的每一行都放在list容器中,再輸出。
全部代碼:
package myDatebases_Demo; import java.sql.*; import java.util.ArrayList; public class BookDb { public static void addBook(Book s) { try { Connection conn = JdbcUtil.getConnection(); PreparedStatement pst = conn.prepareStatement("insert into book values (?,?,?)"); pst.setInt(1, s.getId()); pst.setString(2, s.getName()); pst.setString(3, s.getPress()); pst.executeUpdate(); JdbcUtil.close(pst, conn); } catch (SQLException ex) { ex.printStackTrace(); } } //根據(jù)書本id刪除書本信息 public static void delectBook(int id){ try { Connection conn = JdbcUtil.getConnection(); PreparedStatement pst = conn.prepareStatement("delete from book where id = ?"); pst.setString(1, String.valueOf(id)); pst.executeUpdate(); JdbcUtil.close(pst, conn); } catch (SQLException ex) { ex.printStackTrace(); } } //根據(jù)書本id更新書本信息 public static void updateBook(Book b, int id) { try { Connection conn = JdbcUtil.getConnection(); PreparedStatement pst = conn.prepareStatement("update book set id = ?,name = ?,press = ? where id = ? "); pst.setInt(1, b.getId()); pst.setString(2, b.getName()); pst.setString(3, b.getPress()); pst.executeUpdate(); JdbcUtil.close(pst, conn); } catch (SQLException ex) { ex.printStackTrace(); } } //根據(jù)書本id查詢書本的信息 //保存在一個對象中,用于集中輸出,或者放在集合中,用于管理輸出等 public static Book findBookById(int id) { Book b = new Book(); try { Connection conn = JdbcUtil.getConnection(); PreparedStatement pst = conn.prepareStatement("select * from book where id = ?"); pst.setInt(1, id); ResultSet rs = pst.executeQuery(); if (rs.next()) { b.setId(rs.getInt(1)); b.setName(rs.getString(2)); b.setPress(rs.getString(3)); } JdbcUtil.close(rs, pst, conn); } catch (SQLException ex) { ex.printStackTrace(); } return b; } //查詢書本的所有信息,保存在一個集合中,后來再通過集合再把所有結(jié)果輸出出來,避免此中語句過于冗雜,利于以后的更改 public static ArrayList<Book> queryBook() { ArrayList<Book> list = new ArrayList<>(); try { Connection conn = JdbcUtil.getConnection(); PreparedStatement pst = conn.prepareStatement("select * from book"); ResultSet rs = pst.executeQuery(); while (rs.next()) { Book b = new Book(); b.setId(rs.getInt(1)); b.setName(rs.getString(2)); b.setPress(rs.getString(3)); list.add(b); } JdbcUtil.close(rs,pst,conn); } catch (SQLException ex) { ex.printStackTrace(); } return list; } }
3.testMain類,運行處
import java.io.IOException; import java.util.ArrayList; import java.util.Collection; import java.util.Iterator; import java.util.Scanner; public class testMain { public static void main(String[] args) throws IOException { Scanner sc = new Scanner(System.in); while (true) { Meau(); int choose = sc.nextInt(); switch (choose) { case 1: Book b = getBook(); BookDb.addBook(b); //看了好多文章,好像都無法實現(xiàn)清空控制臺的功能,這個是最實用的 break; case 2: System.out.println("請輸入想要刪除的書本的id"); int id = sc.nextInt(); BookDb.delectBook(id); break; case 3: System.out.println("請輸入想要更新的書本的id"); Book b1 = getBook(); int id1 = sc.nextInt(); BookDb.updateBook(b1,id1); break; case 4: System.out.println("請輸入想要查詢的書本的id"); int id2 = sc.nextInt(); Book b2 = BookDb.findBookById(id2); System.out.println("id\t\t name\t\t press\t"); System.out.println(b2.getId()+"\t\t"+b2.getName()+"\t\t"+b2.getPress()); break; case 5: System.out.println(BookDb.queryBook().size()); queryBook(BookDb.queryBook()); break; } } } public static void queryBook(ArrayList<Book> list){ System.out.println("id\t\t name\t\t press\t"); for (int i = 0; i < list.size(); i++) { System.out.println(list.get(i).getId()+"\t\t"+list.get(i).getName()+"\t\t"+list.get(i).getPress()+"\t"); } } public static Book getBook(){ Book b = new Book(); Scanner sc = new Scanner(System.in); System.out.println("請輸入書本id:"); b.setId(sc.nextInt()); System.out.println("請輸入書本名稱:"); b.setName(sc.next()); System.out.println("請輸入書本出版社名稱: "); b.setPress(sc.next()); return b; } public static void Meau(){ System.out.println("歡迎進入圖書館管理系統(tǒng)"); System.out.println("1 增加"); System.out.println("2 刪除"); System.out.println("3 修改"); System.out.println("4 查詢"); System.out.println("5 顯示全部信息"); System.out.println("6 退出"); } }
看一下效果
嘿嘿,這樣我們就完成啦!!
總結(jié)
到此這篇關于配置idea將Java與數(shù)據(jù)庫連接起來實現(xiàn)一個簡單的圖書管理系統(tǒng)的文章就介紹到這了,更多相關Java與數(shù)據(jù)庫實現(xiàn)圖書管理系統(tǒng)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!
相關文章
mybatis注解動態(tài)sql注入map和list方式(防sql注入攻擊)
這篇文章主要介紹了mybatis注解動態(tài)sql注入map和list方式(防sql注入攻擊),具有很好的參考價值,希望對大家有所幫助。2021-11-11