Simple example of jdbc-mysql base additions and deletions

Li Wu:
Learn to think more, honouring teachers save Thanksgiving. Leaf See Root 321, rivers with one.
Meekness Conscience Lord, willing to do without regrets to the most bitter. Reading exercise strong body and mind, Prudential advised and the line and cherish.

Data,

data, Lingen on the data. Cloud computing, AI and other technologies are based on data. You must be cautious about operating the database. Give the most bitter code here, and take a look at it, to have your own judgment. Meet the choice, or ashamed to ask.

Javase:8
mysql:5.7.14
mysql-connector-java:5.1.44
Os:windows7 x64
Ide:myeclipse

Project structure

Here is the code presentation for each class under each package

Com.jizuiku.jdbc

-Jdbcutils

Package Com.jizuiku.jdbc;import Java.sql.connection;import Java.sql.drivermanager;import Java.sql.preparedstatement;import Java.sql.resultset;import Java.sql.sqlexception;import  com.jizuiku.jdbc.dao.daoexception;/** * * * * @author to the hardest * @version V17.11.07 */public final class Jdbcutils {/** * URL format -JDBC: Sub-Protocol: Sub-name//hostname: Port number/database name? property Name = Property Value & Property Name = property Value * There are multiple parameters in the configstring variable, which requires deep study of their specific meanings */private static String configstring = "? useunicode=true&characterencoding=utf8&usessl=true";p rivate static String url = "JDBC: Mysql://localhost:3306/jdbcforjava "+ configstring;//local MySQL database (No child name) port number 3306 database Jdbcforjavaprivate static String user = "root";p rivate static String password = "";//tool class, direct use, not raw object private jdbcutils () {}//put the registration driver into the static code block, because the static code block executes only once STA Tic {try {class.forname ("Com.mysql.jdbc.Driver"),} catch (ClassNotFoundException e) {//TODO auto-generated catch block/ /exception to throw out throw new Exceptionininitializererror (e);}} /** * Get a link to a specified database * */public static Connection getconnection() throws SQLException {return drivermanager.getconnection (URL, user, password);} /** * Frees three resources ResultSet Statement Connection * */public static void Free (ResultSet rs, preparedstatement PS, Connection con {try {if (rs! = null) {Rs.close ()}} catch (SQLException e) {//TODO auto-generated catch Blockthrow new Daoexception (e) ;} Finally {try {if (PS! = null) {Ps.close ()}} catch (SQLException e) {//TODO auto-generated catch Blockthrow new daoexcept Ion (e);} Finally {try {if (con! = null) {Con.close ()}} catch (SQLException e) {//TODO auto-generated catch Blockthrow new Daoexce Ption (e);}}}}

Com.jizuiku.jdbc.dao

-Bookdao

Package Com.jizuiku.jdbc.dao;import com.jizuiku.jdbc.domain.book;/** * This interface can separate the business logic layer from the data access layer, which is a strong idea.  *  * @author Blog Park-give the most bitter * @version V17.11.08 */public interface Bookdao {/** * Add new book *  */public void Addbook (Boo K book);/** * Query the information of related books according to ID, because ID is unique *  */public book getBook (int id);/** * Update the books in the database *  * */public void Updatebook /** * Delete a specified book in a database *  * */public void Deletebook;

-Daoexception

Package com.jizuiku.jdbc.dao;/** * This class is important, that is, to complete the action of throwing exceptions, by compiling, but also to keep the interface of the data logic layer concise. *  * @author Blog Park-to the most bitter * @version V17.11.08 */public class Daoexception extends RuntimeException {/** *  */private STA Tic final Long serialversionuid = 1l;public daoexception () {//TODO auto-generated constructor stub}public daoexception (St Ring message) {super (message);//TODO auto-generated constructor stub}public daoexception (Throwable cause) {Super (cause );//TODO auto-generated constructor stub}public daoexception (String message, Throwable cause) {Super (message, cause);// TODO Auto-generated Constructor stub}public daoexception (String message, Throwable cause, Boolean enablesuppression, Boolean writablestacktrace) {super (message, cause, enablesuppression, writablestacktrace);//TODO auto-generated Constructor stub}}

-Daofactory

Package Com.jizuiku.jdbc.dao;import Java.io.inputstream;import java.util.properties;/** * Factory class-Singleton mode * * @author Blog Park-to the most bitter * @version V17.11.08 */public class Daofactory {/* * There's a big hole here: just change the two words. Then the returned Bookdao is always null * why? * Because think of the order of initialization, new Daofactory () after arduous bookdao to fix, * immediately followed by Bookdao Bookdao = null;  Naught * */private static Bookdao Bookdao = Null;private static Daofactory instance = new Daofactory ();/** * Properties + Reflection Initialization * This way, the most bitter to the new implementation of the interface, a new class has been born. Then only need to change the configuration file is OK * God, this is what the predecessor thought of ... * * */private daofactory () {//TODO auto-generated constructor stub//here use properties to read the configuration file//can also use xmlproperties prop = NE W Properties (); InputStream InputStream = null;try {//This write, very strong! InputStream = DaoFactory.class.getClassLoader (). getResourceAsStream ("bookdao.properties");//Load Profile Prop.load via stream ( InputStream);/* * Bookdao=com.jizuiku.jdbc.dao.impl.bookdaojdbcimpl * Key:bookdao * Value: Com.jizuiku.jdbc.dao.impl.BookDaoJDBCImpl * value is just the implementation class of the Bookdao interface */string Bookdaoimplclasspath = ProP.getproperty ("Bookdao");//With Bookdaoimplclasspath, you can use reflection to construct this class Bookdao = (Bookdao) class.forname ( Bookdaoimplclasspath). newinstance ();} catch (Exception e) {//Try all the code is the process of initialization, so if there are errors, then direct error! throw new Exceptionininitializererror (e);}} public static Daofactory getinstance () {return instance;} Public Bookdao Getbookdao () {return Bookdao;}}

Com.jizuiku.jdbc.dao.impl

-Bookdaojdbcimpl

Package Com.jizuiku.jdbc.dao.impl;import Java.sql.connection;import Java.sql.preparedstatement;import Java.sql.resultset;import Java.sql.sqlexception;import Java.sql.statement;import com.jizuiku.jdbc.JDBCUtils; Import Com.jizuiku.jdbc.dao.bookdao;import Com.jizuiku.jdbc.dao.daoexception;import Com.jizuiku.jdbc.domain.Book /** * * * @author Blog Park-give the most bitter * @version V17.11.08 */public class Bookdaojdbcimpl implements Bookdao {/** * Add operation * */@Ov erridepublic void Addbook (book book) {//TODO auto-generated method stubconnection con = null; PreparedStatement PS = null; ResultSet rs = null;try {//Register driver and establish connection con = jdbcutils.getconnection ();//Create a statement and preprocess the SQL statement String sql = "INSERT INTO book (Name,quantity,time,price) VALUES (?,?,?,?) "; /Note to return the primary key PS = Con.preparestatement (Sql,statement.return_generated_keys);//pass in the value to the SQL statement ps.setstring (1, Book.getname () );p S.setint (2, Book.getquantity ());p s.setdate (3, New Java.sql.Date (Book.gettime (). GetTime ()));p S.setbigdecimal (4, Book.getprice ());//Execute SQL statement PS.EXECUTEUPdate ();//Get primary key//Because the primary key is one, and int id = 0;RS = Ps.getgeneratedkeys (), if (Rs.next ()) {//The object is successfully inserted into the database, Give the obtained primary key to this book object id = rs.getint (1); Book.setid (ID);} } catch (SQLException e) {//TODO auto-generated catch block//Here for exception handling, contains wisdom, very key! throw new Daoexception (E.getmessage (), e);} finally {//release resource Jdbcutils.free (RS, ps, con);}} /** * * */@Overridepublic book getBook (int id) {//TODO auto-generated method stubconnection con = null; PreparedStatement PS = null; ResultSet rs = null; Book book = null;try {//Register driver and establish connection con = jdbcutils.getconnection ();//create statement String sql = "Select Id,name,quantity,time,price From book where id=? "; PS = con.preparestatement (sql);p S.setint (1, id);//EXECUTE Statement rs = Ps.executequery ();//processing result if (Rs.next ()) {//have query to, Then create an object Sbook = Mappingbook (RS);}} catch (SQLException e) {//TODO auto-generated catch block//Here for exception handling, contains wisdom, very key! throw new Daoexception (E.getmessage (), e);} finally {//release resource Jdbcutils.free (RS, ps, con);} return book;} /** * Modify Operation * */@Overridepublic void updateBook Book {//TODO auto-generated method stubconnection con = null; PreparedStatement PS = null; ResultSet rs = null;try {//Register driver and establish connection con = jdbcutils.getconnection ();//create statement String sql = "Update book set name=?,quantity= ?, time=?,price=? where id=? "; PS = con.preparestatement (SQL);//pass in a value to the SQL statement ps.setstring (1, Book.getname ());p S.setint (2, book.getquantity ());//Convert From Date under Util packet to dateps.setdate under SQL Package (3, New Java.sql.Date (Book.gettime (). GetTime ()));p S.setbigdecimal (4, Book.getprice ());p S.setint (5, Book.getid ());//EXECUTE Statement ps.executeupdate ();} catch (SQLException e) {//TODO auto-generated catch block//Here for exception handling, contains wisdom, very key! throw new Daoexception (E.getmessage (), e);} finally {//release resource Jdbcutils.free (RS, ps, con);}} /** * Delete operation * * */@Overridepublic void Deletebook (book book) {//TODO auto-generated method stubconnection con = null; PreparedStatement PS = null; ResultSet rs = null;try {//Register driver and establish connection con = jdbcutils.getconnection ();//create statement String sql = "Delete from book where id=?"; PS = con.preparestAtement (SQL);p s.setint (1, Book.getid ());//EXECUTE Statement ps.executeupdate ();} catch (SQLException e) {//TODO auto-generated catch block//Here for exception handling, contains wisdom, very key! throw new Daoexception (E.getmessage (), e);} finally {//release resource Jdbcutils.free (RS, ps, con);}}  /** * Assigns a value to the book object based on the result of the query and returns an assigned Object * */private book Mappingbook (ResultSet rs) throws SQLException {//The benefit of this is: reduce code reuse// Convenient for later optimization book book = new book (), Book.setid (Rs.getint ("id")), Book.setname (rs.getstring ("name")); Book.setquantity ( Rs.getint ("Quantity")), Book.settime (Rs.gettimestamp ("Time")), Book.setprice (Rs.getbigdecimal ("price"); return Book;}}

Com.jizuiku.jdbc.domain

-Book

Package Com.jizuiku.jdbc.domain;import java.math.bigdecimal;/** * The book object corresponds to the representation of the database * * @author Blog Park-give the most bitter * @version V17.11. */public class Book {private int id;private String name;private int quantity;private java.util.Date time;private bigdec iMAL price;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 int getquantity () {return quantity;} public void setquantity (int quantity) {this.quantity = quantity;} Public Java.util.Date GetTime () {return time;} public void SetTime (Java.util.Date time) {this.time = time;} Public BigDecimal GetPrice () {return price;} public void Setprice (BigDecimal price) {this.price = Price;}  @Overridepublic int hashcode () {final int prime = 31;int result = 1;result = Prime * result + Id;result = Prime * result + ((name = = null)? 0:name.hashcode ()); result = Prime * result + ((Price = = null)? 0:price.hashcode ()); result = Prime * result + Quantity;rEsult = Prime * result + ((time = = null)? 0:time.hashcode ()); return result; @Overridepublic boolean equals (Object obj) {if (this = = obj) return true;if (obj = = null) return False;if (GetClass ()! = obj . GetClass ()) return false; Book other = (book) obj;if (id! = other.id) return false;if (name = = null) {if (other.name! = null) return false;} else if ( !name.equals (Other.name)) return false;if (Price = = null) {if (Other.price! = null) return false;} else if (!price.equals (OT Her.price)) return false;if (Quantity! = other.quantity) return false;if (time = null) {if (other.time! = null) return False ;} else if (!time.equals (other.time)) return False;return true;} @Overridepublic String toString () {return "book [id=" + ID + ", name=" + name + ", quantity=" + Quantity + ", time=" + Tim E + ", price=" + price+ "]";}}

Data in the database

The following to make additions and deletions to check the test!

Com.jizuiku.jdbc.demo

-Demo

Delete Test

Package Com.jizuiku.jdbc.demo;import Java.math.bigdecimal;import Java.util.date;import Com.jizuiku.jdbc.dao.bookdao;import com.jizuiku.jdbc.dao.daofactory;import com.jizuiku.jdbc.domain.book;/** * Test class *  * @author Blog Park-to the most bitter * @version V17.11.08 */public class Demo {public static void main (string[] args) {//Use factory class, extensibility becomes stronger! Bookdao Bookdao = Daofactory.getinstance (). Getbookdao (); Book.setid (15);//delete test/delete id=15 record Bookdao.deletebook (book); System.out.println (Book.tostring ());}}

The state of the database before running the code

Run the code, the output of the console is

　　

Data changes in the database

Add test

Package Com.jizuiku.jdbc.demo;import Java.math.bigdecimal;import Java.util.date;import Com.jizuiku.jdbc.dao.bookdao;import com.jizuiku.jdbc.dao.daofactory;import com.jizuiku.jdbc.domain.book;/** * Test class *  * @author Blog Park-to the most bitter * @version V17.11.08 */public class Demo {public static void main (string[] args) {//Use factory class, extensibility becomes stronger! Bookdao Bookdao = Daofactory.getinstance (). Getbookdao (); Book book = new book (), Book.setname ("Mencius"), book.setquantity (+), Book.settime (New Date ()), Book.setprice (New BigDecimal (Double.tostring (99.99))); /Add Test complete!        Bookdao.addbook (book); System.out.println (Book.tostring ());} }

The state of the database before running the code

Run the code, the output of the console is

Data changes in the database

Find Tests

Package Com.jizuiku.jdbc.demo;import Java.math.bigdecimal;import Java.util.date;import Com.jizuiku.jdbc.dao.bookdao;import com.jizuiku.jdbc.dao.daofactory;import com.jizuiku.jdbc.domain.book;/** * Test class *  * @author Blog Park-to the most bitter * @version V17.11.08 */public class Demo {public static void main (string[] args) {//Use factory class, extensibility becomes stronger! Bookdao Bookdao = Daofactory.getinstance (). Getbookdao (); Book book = new book ();//Find Test complete! Book = Bookdao.getbook (5); System.out.println (Book.tostring ());} }

Data stored in the database

After running the code, the results of the console output

Note: This query for the most bitter is queried by the ID value and the result is unique. To the most bitter query that does not achieve the result is not unique.

Modify Test

Package Com.jizuiku.jdbc.demo;import Java.math.bigdecimal;import Java.util.date;import Com.jizuiku.jdbc.dao.bookdao;import com.jizuiku.jdbc.dao.daofactory;import com.jizuiku.jdbc.domain.book;/** * Test class *  * @author Blog Park-to the most bitter * @version V17.11.08 */public class Demo {public static void main (string[] args) {//Use factory class, extensibility becomes stronger! Bookdao Bookdao = Daofactory.getinstance (). Getbookdao (); Book book = new book (); Book.setid (6); Book.setname ("Mencius"); book.setquantity (+); Book.settime (new Date ()); Book.setprice (New BigDecimal (Double.tostring (99.99));//change Test complete!        //Is the Bookdao.updatebook (book) changed according to the ID value; System.out.println (Book.tostring ());} }

Before executing the code, the contents of the database

After executing the code, the output of the console

Changes to the database

Okay, here's the end of the blog post. It's finally over. Tired of writing to the most bitter.

The "Diamond Sutra" said: "No I have no life without life." Fix all good laws. ", so to the most bitter here will not beg for a Thanksgiving heart." However, it is said that if the intention, in the heart say thank you Bai. Although the most bitter technology is poor, the level of this blog post can only be used for reference. However, this insistence on the most bitter have been moved bad.

Or the words at the beginning of the blog:

Data, data, Lingen on the data. Cloud computing, AI and other technologies are based on data. You must be cautious about operating the database. Give the most bitter code here, and take a look at it, to have your own judgment. Meet the choice, or ashamed to ask.

Come on!

Learning resources: Itcast and Itheima Video library. If you have public resources, can share to me, with your resources to learn also can.
Blog post is to watch the video, into thinking written. It's good that the teacher speaks well. Blog bad, is to give the most bitter not serious.

Simple example of jdbc-mysql base additions and deletions