Java Database Operations: add, delete, modify, query, and add, delete, and delete java Database Operations

Java Database Operations: add, delete, modify, query, and add, delete, and delete java Database Operations

Not much bb.

Tool: myeclipse 2016, mysql 5.7

Objective: to add, delete, modify, and query product information in a java Database

Goods table of the test Database

Gid primary key, auto-Increment

1. Object Goods: encapsulate database data (classes that match fields in the database table)

// Entity class public class Goods {private int gid; private String gname; private double GPRS; private String gdate; // generate get and set Methods public int getGid () {return gid;} public void setGid (int gid) {this. gid = gid;} public String getGname () {return gname;} public void setGname (String gname) {this. gname = gname;} public double getuplice () {return uplice;} public void setuplice (double uplice) {this.. gdate = gdate;} // build method public Goods (int gid, String gname, double uplice, String gdate) {super (); this. gid = gid; this. gname = gname; this. uplice = uplice; this. gdate = gdate;} // generate the construction method without parameters public Goods () {super ();}}

2. Implementation class GoodsDao (without DBHelper): Operate the database to implement addition, deletion, modification, and query

Import java. SQL. connection; import java. SQL. driverManager; import java. SQL. preparedStatement; import java. SQL. resultSet; import java. SQL. statement; import java. util. optional; public class GoodsDao {public static void main (String [] args) {GoodsDao dao = new GoodsDao (); // dao. add (); // dao. del (); // dao. upd (); partition input = new partition (System. in); System. out. println ("Enter the product name:"); String name = input. next (); System. out. println ("Enter the product price:"); double price = input. nextDouble (); dao. newAdd (name, price); // System. out. println ("Enter the lowest price:"); // double price = input. nextDouble (); // dao. search (price); // System. out. println ("Operation completed successfully! ");} // Add public void add () {try {// 1. through reflection, load the driver Class to the jvm Class. forName ("com. mysql. jdbc. driver "); // 2. obtain the database Connection object Connection conn = DriverManager. getConnection ("jdbc: mysql: // localhost: 3306/test? CharacterEncoding = utf8 & useSSL = true "," root "," "); // 3. create Database Operation object Statement stmt = conn. createStatement (); // 4. operation database stmt.exe cute ("insert into goods (gname, uplice, gdate) values ('ggg ', '2. 33 ', now () "); // 5. close each resource stmt. close (); conn. close ();} catch (Exception e) {e. printStackTrace () ;}// Delete public void del () {try {Class. forName ("com. mysql. jdbc. driver "); Connection conn = DriverManager. getConnection ("j Dbc: mysql: // localhost: 3306/test? CharacterEncoding = utf8 & useSSL = true "," root "," "); Statement stmt = conn. createStatement (); stmt.exe cute ("delete from goods where gid = 1"); stmt. close (); conn. close ();} catch (Exception e) {e. printStackTrace () ;}// change the public void upd () {try {Class. forName ("com. mysql. jdbc. driver "); Connection conn = DriverManager. getConnection ("jdbc: mysql: // localhost: 3306/test? CharacterEncoding = utf8 & useSSL = true "," root "," "); Statement stmt = conn. createStatement (); stmt.exe cute ("update goods set gname = 'ggg 'where gid = 1"); stmt. close (); conn. close ();} catch (Exception e) {e. printStackTrace () ;}// query public void search (double price) {try {Class. forName ("com. mysql. jdbc. driver "); Connection conn = DriverManager. getConnection ("jdbc: mysql: // localhost: 3306/test? CharacterEncoding = utf8 & useSSL = true "," root "," "); Statement stmt = conn. createStatement (); // result set object ResultSet rs = stmt.exe cuteQuery ("select * from goods where uplice>" + price); while (rs. next () {System. out. println (rs. getString (1) + "#" + rs. getString ("gname") + "#" + rs. getDouble ("uplice") + "#" + rs. getString ("gdate");} rs. close (); stmt. close (); conn. close ();} catch (Exception e) {e. printStackTrace () ;}// use Add public void newAdd (String gname, double uplice) {try {Class. forName ("com. mysql. jdbc. driver "); Connection conn = DriverManager. getConnection ("jdbc: mysql: // localhost: 3306/test? CharacterEncoding = utf8 & useSSL = true "," root "," "); // Statement stmt = conn. createStatement (); // stmt.exe cute ("insert into goods (gname, uplice, gdate) values ('" + gname + "', '" + uplice + "', now () "); // stmt. close (); // String spelling simpler String SQL = "insert into goods (gname, uplice, gdate) values (?,?, Now () "; PreparedStatement pstmt = conn. prepareStatement (SQL); pstmt. setString (1, gname); pstmt. setDouble (2, uplice); pstmt.exe cute (); conn. close ();} catch (Exception e) {e. printStackTrace ();}}}

3. DBHelper class: Solves the repetitive work of operating the database with the above Code

Import java. SQL. connection; import java. SQL. driverManager; import java. SQL. preparedStatement; import java. SQL. resultSet; import java. SQL. statement; /*** get the connection object of the database operation * close various resources of the database operation * @ author **/public class DBHelper {private static final String className = "com. mysql. jdbc. driver "; private static final String url =" jdbc: mysql: // localhost: 3306/test? CharacterEncoding = utf8 & useSSL = true "; private static final String uname =" root "; private static final String upass = ""; /*** method for obtaining database Connection objects */public static Connection getConn () {Connection conn = null; try {Class. forName (className); conn = DriverManager. getConnection (url, uname, upass);} catch (Exception e) {e. printStackTrace ();} return conn;}/*** closes the database connection object */public static void closeConn (Conne Ction conn) {try {if (conn! = Null) {conn. close () ;}} catch (Exception e) {e. printStackTrace () ;}}/*** shut down the database operation object */public static void closeStmt (Statement stmt) {try {if (stmt! = Null) {stmt. close () ;}} catch (Exception e) {e. printStackTrace () ;}}/*** shut down the database operation object */public static void closePstmt (PreparedStatement pstmt) {try {if (pstmt! = Null) {pstmt. close () ;}} catch (Exception e) {e. printStackTrace () ;}}/*** shut down the database operation object */public static void closeRs (ResultSet rs) {try {if (rs! = Null) {rs. close () ;}} catch (Exception e) {e. printStackTrace ();}}}

4. Implementation class NewDao (with DBHelper): perform database operations to add, delete, modify, and query

Import java. SQL. connection; import java. SQL. preparedStatement; import java. SQL. resultSet; import java. util. arrayList; import java. util. list; import java. util. required; public class NewDao {public static void main (String [] args) {NewDao nd = new NewDao (); nd. show (); // specify input = new partition (System. in); // modify the data // System. out. println ("Enter the product ID to be modified:"); // int gid = input. nextInt (); // System. out. println ("Enter Product name to be modified: "); // String gname = input. next (); // System. out. println ("Enter the price of the product to be modified:"); // double uplice = input. nextDouble (); // nd. upd (gid, gname, uplice); // query data // System. out. println ("Enter the lowest price:"); // double mprice = input. nextDouble (); // System. out. println ("Enter the maximum price:"); // double xprice = input. nextDouble (); // nd. search (mprice, xprice);} private Connection conn = null; private PreparedStatement pstm T = null; private ResultSet rs = null; // query public void search (double minprice, double maxprice) {try {conn = DBHelper. getConn (); String SQL = "select * from goods where GPRS >=? And uplice <=? "; // Pre-compiled object pstmt = conn. prepareStatement (SQL); pstmt. setDouble (1, minprice); pstmt. setDouble (2, maxprice); rs = pstmt.exe cuteQuery (); while (rs. next () {System. out. println (rs. getString ("gid") + "#" + rs. getString ("gname") + "#" + rs. getString ("uplice") ;}} catch (Exception e) {e. printStackTrace ();} finally {DBHelper. closeRs (rs); DBHelper. closePstmt (pstmt); DBHelper. closeConn (conn) ;}// change the public void of the data. Upd (int gid, String gname, double uplice) {try {conn = DBHelper. getConn (); String SQL = "update goods set gname = ?, GPRS =? Where gid =? "; Pstmt = conn. prepareStatement (SQL); pstmt. setString (1, gname); pstmt. setDouble (2, uplice); pstmt. setInt (3, gid); pstmt.exe cute ();} catch (Exception e) {e. printStackTrace ();} finally {DBHelper. closePstmt (pstmt); DBHelper. closeConn (conn) ;}}// use a set to display the public List of data <Goods> getAllGoods () {List <Goods> list = new ArrayList <Goods> (); try {conn = DBHelper. getConn (); String SQL = "select * from goods"; pstmt = conn. prepareStatement (SQL); rs = pstmt.exe cuteQuery (); while (rs. next () {Goods goods = new Goods (rs. getInt ("gid"), rs. getString ("gname"), rs. getDouble ("uplice"), rs. getString ("gdate"); list. add (goods) ;}} catch (Exception e) {e. printStackTrace ();} finally {DBHelper. closeRs (rs); DBHelper. closePstmt (pstmt); DBHelper. closeConn (conn);} return list;} public void show () {List <Goods> list = getAllGoods (); for (int I = 0; I <list. size (); I ++) {System. out. println (list. get (I ). getGid () + "#" + list. get (I ). getGname () + "#" + list. get (I ). getuplice ());}}}