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

Source: Internet
Author: User

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 ());}}}

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.