使用工具

IDEA2018.2 MySQL5.6 JDK1.8

mysql-connector-java-5.0.8-bin.jar

源碼下載地址

碼云鏈接：https://gitee.com/vvcat/fruit_store
mysql-connector-java-5.0.8-bin.jar 微云下載鏈接：https://share.weiyun.com/5jp0fIr

環(huán)境搭建

添加mysql-connector-java-5.0.8-bin.jar

將 mysql-connector-java-5.0.8-bin.jar 文件添加到resources目錄下

此時并沒有將 mysql-connector-java-5.0.8-bin.jar 加載到我們的程序中，需要進行以下操作

選擇OK

此時這個jar包導入了我們的項目中，可以查看jar包中的代碼

以下是代碼結(jié)構(gòu)和jar包存放的位置

IDEA連接數(shù)據(jù)庫

IDEA分為
商業(yè)版本：Ultimate Edition；
免費版本：Community Edition；





如果出現(xiàn)Failed 說明 數(shù)據(jù)庫測試鏈接失敗，請檢查Database，User，Password等信息是否輸入正確

出現(xiàn)Successful 說明 數(shù)據(jù)庫 測試連接成功，我們可以使用和調(diào)用數(shù)據(jù)庫的數(shù)據(jù)了。

代碼示例

CREATE DATABASE fruit_store;USE fruit_store;CREATE TABLE fruits(id INT(11) NOT NULL AUTO_INCREMENT,name VARCHAR(50) NULL,price DOUBLE NULL,unit VARCHAR(50) NULL,expiredDate DATE NULL,PRIMARY KEY (id) ) ENGINE=INNODB AUTO_INCREMENT=1 CHARSET=utf8;INSERT INTO fruits (id, name, price, unit, expiredDate) VALUES(1, '香蕉', '10.5', '斤', '2019-9-15'),(2, '蘋果', '1.5', '斤', '2019-9-16');

Main.java

package com.gitee.vvcat;import java.text.ParseException; import java.util.Scanner; import com.gitee.vvcat.ui.AdminController;public class Main {public static void main(String[] args) throws ParseException {AdminController adminController = new AdminController();while (true) {int choice = adminController.menu();switch (choice) {case 1:adminController.queryAll();break;case 2:adminController.addFruit();break;case 3:adminController.updateFruit();break;case 4:adminController.deleteFruit();break;case 5:System.out.println("歡迎下次使用！");return;default:System.out.println("輸入錯誤，請重新輸入！");}}}}

AdminController.java

package com.gitee.vvcat.ui;/*** @Author ??VVcat??* @Date 2019/9/11 11:59* @Version 1.0**/import com.gitee.vvcat.model.Fruit; import com.gitee.vvcat.services.AdminService;import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.Scanner;public class AdminController {AdminService adminService= new AdminService();public int menu() {Scanner scanner = new Scanner(System.in);System.out.println("\n\t\t商品管理系統(tǒng)");System.out.println("\t\t\t1:查詢商品清單");System.out.println("\t\t\t2:添加新信息");System.out.println("\t\t\t3:更新信息");System.out.println("\t\t\t4:刪除信息");System.out.println("\t\t\t5:退出系統(tǒng)");System.out.print("\t\t請選擇你的操作（1-5）：");return scanner.nextInt();}public void queryAll() {ArrayList<Fruit> fruits = adminService.queryFruits();System.out.println("商品的所有清單");System.out.println("編號| 名稱| 價格 | 單位 | 過期日期");for(Fruit item:fruits){System.out.println(item);}}public void addFruit() throws ParseException {SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");Scanner scanner = new Scanner(System.in);System.out.println("添加信息：");System.out.print("請輸入編號：");int id = scanner.nextInt();System.out.print("請輸入名稱：");String name = scanner.next();System.out.print("請輸入價格：");double price = scanner.nextDouble();System.out.print("請輸入單位：");String unit = scanner.next();System.out.print("請輸入過期日期：");Date expiredDate=sdf.parse(scanner.next());if (adminService.addFruit(id,name,price, unit,expiredDate)){System.out.println("\t信息添加成功！");}else {System.out.println("\t信息添加失敗！");}}public void updateFruit() throws ParseException {SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");Scanner scanner = new Scanner(System.in);System.out.println("更新某個信息：");System.out.print("請輸入要更改的編號：");int id = scanner.nextInt();System.out.print("請輸入新的名稱：");String name = scanner.next();System.out.print("請輸入新的價格：");double price = scanner.nextDouble();System.out.print("請輸入新的單位：");String unit = scanner.next();System.out.print("請輸入新的過期日期：");Date expiredDate=sdf.parse(scanner.next());if (adminService.updateFruit(id,name,price, unit,expiredDate)){System.out.println("\t信息更新成功！");}else {System.out.println("\t信息更新失敗！");}}public void deleteFruit() {Scanner scanner = new Scanner(System.in);System.out.println("刪除某個信息：");System.out.print("請輸入要刪除的編號：");int id= scanner.nextInt();if(adminService.deleteFruit(id)){System.out.println("刪除成功！");}else {System.out.println("刪除失敗！");}} }

AdminService.java

package com.gitee.vvcat.services;/*** @Author ??VVcat??* @Date 2019/9/11 11:58* @Version 1.0**/import com.gitee.vvcat.model.*; import com.gitee.vvcat.data.FruitRepository;import java.util.ArrayList; import java.util.Date;public class AdminService {private Repository<Fruit> repository=new FruitRepository();public ArrayList<Fruit> queryFruits(){return repository.all();}public boolean addFruit(int id,String name ,double price,String unit,Date expiredDate){Fruit item = repository.getById(id);if (item==null){Fruit fruit = new Fruit(id, name, price, unit, expiredDate);repository.insert(fruit);return true;}return false;}public boolean updateFruit(int id,String name ,double price,String unit,Date expiredDate){Fruit item = repository.getById(id);if (item!=null){item.setName(name);item.setPrice(price);item.setUnit(unit);item.setExpiredDate(expiredDate);repository.update(item);return true;}return false;}public boolean deleteFruit(int id){Fruit item = repository.getById(id);if (item!=null){repository.delete(id);return true;}return false;} }

Repository.java

package com.gitee.vvcat.model;/*** @Author ??VVcat??* @Date 2019/9/11 11:57* @Version 1.0**/ import java.util.ArrayList;public interface Repository<T> {ArrayList<T> all();T getById(int id);void insert(T entity);void update(T entity);void delete(int id);}

Fruit.java

package com.gitee.vvcat.model;/*** @Author ??VVcat??* @Date 2019/9/11 11:57* @Version 1.0**/ import java.text.SimpleDateFormat; import java.util.Date;public class Fruit {private int id;private String name;private double price;private String unit;private Date expiredDate;public Fruit() {}public Fruit(int id, String name, double price, String unit, Date expiration) {this.id = id;this.name = name;this.price = price;this.unit = unit;this.expiredDate = expiration;}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 String getUnit() {return unit;}public void setUnit(String unit) {this.unit = unit;}public Date getExpiredDate() {return expiredDate;}public void setExpiredDate(Date expiredDate) {this.expiredDate = expiredDate;}@Overridepublic String toString() {SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");return "\t"+getId()+"\t"+getName()+"\t"+getPrice()+"\t"+getUnit()+"\t"+simpleDateFormat.format(getExpiredDate());}public boolean isExpired(){if (new Date().before(expiredDate)){return false;}return true;} }

FruitRepository.java

package com.gitee.vvcat.data;/*** @Author ??VVcat??* @Date 2019/9/11 11:58* @Version 1.0**/import com.gitee.vvcat.model.Fruit; import com.gitee.vvcat.model.Repository; import com.gitee.vvcat.util.DbUtility;import java.sql.*; import java.util.ArrayList; import java.util.Date;public class FruitRepository implements Repository<Fruit> {@Overridepublic ArrayList<Fruit> all() {Connection conn=null;Statement smt=null;ResultSet rs=null;String sql = "select * from fruits";ArrayList<Fruit> result = new ArrayList<Fruit>();try {conn = DbUtility.getConnection();smt=conn.createStatement();rs=smt.executeQuery(sql);while(rs.next()){Fruit fruit = new Fruit();fruit.setId(rs.getInt("id"));fruit.setName(rs.getString("name"));fruit.setPrice(rs.getDouble("price"));fruit.setUnit(rs.getString("unit"));fruit.setExpiredDate(new Date(rs.getDate("expireddate").getTime()));result.add(fruit);}} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}finally {DbUtility.release(smt,conn,rs);}return result;}@Overridepublic Fruit getById(int id) {Connection conn=null;PreparedStatement smt=null;ResultSet rs=null;String sql = "select * from fruits where id=?";try {conn = DbUtility.getConnection();smt=conn.prepareStatement(sql);smt.setInt(1,id);rs=smt.executeQuery();if (rs.next()){Fruit fruit = new Fruit();fruit.setId(rs.getInt("id"));fruit.setName(rs.getString("name"));fruit.setPrice(rs.getDouble("price"));fruit.setUnit(rs.getString("unit"));fruit.setExpiredDate(new Date(rs.getDate("expireddate").getTime()));return fruit;}return null;} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}finally {DbUtility.release(smt,conn,rs);}return null;}@Overridepublic void insert(Fruit entity) {Connection conn=null;PreparedStatement smt=null;String sql = "insert into fruits(id,name,price,unit,expireddate) values(?,?,?,?,?)";try {conn=DbUtility.getConnection();smt=conn.prepareStatement(sql);smt.setInt(1,entity.getId());smt.setString(2,entity.getName());smt.setDouble(3,entity.getPrice());smt.setString(4,entity.getUnit());smt.setDate(5,new java.sql.Date(entity.getExpiredDate().getTime()));smt.executeUpdate();} catch (SQLException e) {e.printStackTrace();} catch (ClassNotFoundException e) {e.printStackTrace();}finally {DbUtility.release(smt,conn);}}@Overridepublic void update(Fruit entity) {Connection conn=null;PreparedStatement smt=null;String sql = "UPDATE fruits SET name=? , price=? ,unit=?,expireddate=? WHERE id=?";try {conn=DbUtility.getConnection();smt=conn.prepareStatement(sql);smt.setString(1,entity.getName());smt.setDouble(2,entity.getPrice());smt.setString(3,entity.getUnit());smt.setDate(4,new java.sql.Date(entity.getExpiredDate().getTime()));smt.setInt(5,entity.getId());smt.executeUpdate();} catch (SQLException e) {e.printStackTrace();} catch (ClassNotFoundException e) {e.printStackTrace();}finally {DbUtility.release(smt,conn);}}@Overridepublic void delete(int id) {Connection conn=null;PreparedStatement smt=null;String sql = "DELETE FROM fruits WHERE id=?";try {conn=DbUtility.getConnection();smt=conn.prepareStatement(sql);smt.setInt(1,id);smt.executeUpdate();} catch (SQLException e) {e.printStackTrace();} catch (ClassNotFoundException e) {e.printStackTrace();}finally {DbUtility.release(smt,conn);}} }

DbUtility.java

package com.gitee.vvcat.util;/*** @Author ??VVcat??* @Date 2019/9/11 11:59* @Version 1.0**/import java.sql.*;public class DbUtility {public static Connection getConnection() throws ClassNotFoundException, SQLException {Class.forName("com.mysql.jdbc.Driver");String url = "jdbc:mysql://localhost:3306/fruit_store";String user = "root";String password = "123456";Connection conn = DriverManager.getConnection(url, user, password);return conn;}public static void release(Statement smt,Connection conn){release(smt);release(conn);}private static<T extends AutoCloseable> void release(T smt) {if (smt!=null){try {smt.close();} catch (SQLException e) {e.printStackTrace();} catch (Exception e) {e.printStackTrace();}smt=null;}}public static void release(Statement smt,Connection conn,ResultSet rs){release(smt);release(conn);release(rs);}}

效果展示

運行

界面

查詢商店清單

添加商品信息

更新商品信息

刪除商品信息

退出系統(tǒng)

如有疑問，請在下方留言

總結(jié)

以上是生活随笔為你收集整理的用Java调用jdbc接口连接MySQL数据库——实现对数据库的增删改查的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。