Java JDBC Connection MySQL database implementation additions and deletions

Haven't written blog for a long time, write a simple thing warm warm up, share to everybody.

JDBC believes that everyone is not unfamiliar, as long as it is a Java, the initial contact with the EE time is to learn such a thing, who called the program and the database to deal with it! And JDBC is a database to deal with a very basic knowledge, but also relatively close to the bottom, in the actual work of the use of more in fact still more mature framework, such as Hibernate, Mybatis.

But as these mature framework of the underlying JDBC is also we should be mastered, only to understand the JDBC additions and deletions, so that in the future if interested to study hibernate or MyBatis source code when it is better to understand how these mature framework is how to achieve the deletion and change.

To get back to the chase, take a look at our development environment:

Java language, Eclipse development tools, MySQL database, navicat database visualization tools.

Installation and use of the development environment please check the information yourself (very simple), here is not elaborated in detail.

The first step, create a database, use the NAVICAT Database visualization tool to build a database, create a table in the library, a few fields (remember to give ID field, unique primary key, self-increment sequence), and then give two data is good, to test the function,

The second step, to get through the database (this example wants you to knock on their own, not a lot of time, familiar with how JDBC and database dealings, it is illustrated),

The third step, the transformation of the Dbutil class, convenient for the DAO layer to get the database connection, the code is as follows:

1 packagecom.czgo.db; 2 3 ImportJava.sql.Connection; 4 Import Java.sql.DriverManager; 5 import  java.sql.SQLException; 6 7 public class  Dbutil 8  {9 Private St Atic final String URL = "Jdbc:mysql://127.0.0.1:3306/imooc" ; the private static final String UNAME = "root" ; p Rivate static final String PWD = "root" ; private static Connection conn = null ; STATIC16  {17 Try18  {19//1. Load driver Class.forName ("Com.mysql.jdbc.Driver" ); 21//2. Get a connection to the database. conn =  Drivermanager.getconnection (URL, UNAME, PWD); }24 catch  (classnotfoundexception e)  {n  e.p Rintstacktrace (); }28 catch  (SQLException e)  {$  e.printstacktrace (); }32 } public static  Connection getconnection ()  {$ return  conn;37 }38}    span>                 

Fourth step, create the entity class (for example, we will use the MVC idea to design This example, there is the idea of MVC design, please learn by yourselves, here not to say) the code is as follows:

1 packageCom.czgo.model; 2 3 Importjava.io.Serializable; 4 5/** 6 * Entity class: Goddess Class 7 * 8 * @author Alanlee 9 * */11 public class Goddess implementsSerializable12{private static final long Serialversionuid = 1L; 14 15/**16 * Unique PRIMARY key */18 privateInteger id;19/**20 * Name */22 privateString name;23/**24 * Mobile number */26 PrivateString mobie;27/**28 * e-mail */30 privateString email;31/**32 * Home Address */34 PrivateString address;35 PublicInteger GetId () 37{returnid;39}40-public voidSETID (Integer ID) 42 {this.id =  id;44 }45 public  String getName () +  {$ return  name;49 }50 -public void  SetName (String name)  {this.name =  name;54 }55-Public  String Getmobi E ()  mobie;59 }60-this.mobie public void  Setmobie (String Mobie) () an> mobie;64 }65, public  String getemail (),  {email;69 }70  void
                                       
                                         setemail (string email), 
                                         {this.email =  email;74 }75 public  String getaddress () {$ return  address;79 }80 bay public void  setaddress (String address) this.address address;84 }85}         
                                             

The fifth step, the implementation of the DAO layer (here because it is a small example does not write the DAO interface, the actual work of the large project should be to write the DAO interface, easy to maintain and extend the program), the code is as follows:

1 packageCom.czgo.dao; 2 3 ImportJava.sql.Connection; 4 ImportJava.sql.PreparedStatement; 5 ImportJava.sql.ResultSet; 6 ImportJava.sql.SQLException; 7 ImportJava.util.ArrayList; 8 ImportJava.util.List; 9 ImportCom.czgo.db.DBUtil; Importcom.czgo.model.Goddess; 12 13/** 14 * Data-tier processing class * * * @author Alanlee * * * * */public classGoddessdao 20{21/** 22 * Query All Goddesses * @return * @throws SQLException/list<goddess> query () throwsSQLException 28{list<goddess> goddesslist = new arraylist<goddess>(); 30 31//Get database connection Connection conn =Dbutil.getconnection (); StringBuilder sb = newStringBuilder (); Sb.append ("Select Id,name,mobie,email,address from Goddess"); 36 37//Through database connection operation database, to achieve additions and deletions to search preparedstatement ptmt =Conn.preparestatement (Sb.tostring ()); ResultSet rs =Ptmt.executequery (); Goddess Goddess = null; While(Rs.next ()) 45{goddess = newGoddess (); Goddess.setid (Rs.getint ("id"))); Goddess.setname (rs.getstring ("name")); Goddess.setmobie (rs.getstring ("Mobie")); Goddess.setemail (rs.getstring ("email")); Wuyi goddess.setaddress (rs.getstring ("Address")); 52 53Goddesslist.add (goddess); 54} returnGoddesslist; 56} 57 58/** 59 * Query Single Goddess * * @return * @throws SQLException * * * Goddess Querybyid (Integer id) throwsSQLException 65{Goddess G = null; Connection conn =Dbutil.getconnection (); The String sql = "" + "SELECT * from imooc_goddess" + "where id=?"; PreparedStatement ptmt =Conn.preparestatement (SQL); Ptmt.setint (1, id); ResultSet rs =Ptmt.executequery (); The while(Rs.next ()) 79{g = newGoddess (); Bayi G.setid (rs.getint ("id")); G.setname (rs.getstring ("name"))); G.setmobie (rs.getstring ("Mobie"))); G.setemail (rs.getstring ("email")); G.setaddress (rs.getstring ("Address")); 86} The returnG 89} 90 91/** 92 * Add Goddess * 94 * @throws SQLException (goddess Goddess) throwsSQLException 97{98//Get database connection Connection conn =Dbutil.getconnection (); 101 String sql = "INSERT INTO Goddess (name,mobie,email,address) VALUES (?,?,?,?)"; 102 103 PreparedStatement ptmt =Conn.preparestatement (SQL); 104 ptmt.setstring (1, Goddess.getname ()); 106 Ptmt.setstring (2, Goddess.getmobie ()); 107 Ptmt.setstring (3, Goddess.getemail ()); 108 Ptmt.setstring (4, goddess.getaddress ()); 109 110Ptmt.execute (); 111}112 113/**114 * Modified Goddess Data * * * @throws SQLException117 */118 public void updategoddess (goddess Goddess) throwsSQLException119 {Connection conn =  dbutil.getconnection (); 121 122 String sql = "Update goddess set name=?,mobie=?,email=?" , address=? where id=? " ; 123 124 PreparedStatement ptmt =  conn.preparestatement (SQL); 126 ptmt.setstring (1 , Goddess.getname ()); 127 ptmt.setstring (2 , Goddess.getmobie ()); Ptmt.setstring (3 , Goddess.getemail ()) ; 129 ptmt.setstring (4 , goddess.getaddress ()); 131  Ptmt.execute ();}133 /**135 134 * Delete Goddess 136 * 13 7 * @throws SQLException138 */139 public void deletegoddess (Integer id) throws  SQLException140  {141 Connectio N conn =  dbutil.getconnection (); 142 143 String sql = "Delete from goddess where id=?" ; 144 145 PreparedStatement ptmt =  conn.preparestatement (SQL) 146 147 ptmt.setint (1 , id); 148 149  Ptmt.execute (); }151}            

The sixth step, the control layer of the implementation (control layer is used here to imitate the control layer and interface, directly here to build data, if the interface data is passed through the request to receive parameters can be, control layer code can be changed according to the actual situation to improve, here is just to give you a simple test, time is relatively tight, I hope you understand), the code is as follows:

1 packageCom.czgo.action; 2 3 ImportJava.sql.SQLException; 4 ImportJava.util.List; 5 6 ImportCom.czgo.dao.GoddessDao; 7 Importcom.czgo.model.Goddess; 8 9/**10 * Control layer, directly here to build data, interface data through the request to receive, also is the same * * * @author ALANLEE13 * */15 public classGoddessAction16{17/**18 * New Goddess * * @param goddess21 * @throws Exception22 */23 public void Add (Goddess Goddess) throwsException24{Goddessdao DAO = newGoddessdao (); Goddess.setname ("Cang jing Empty"); Goddess.setmobie ("52220000")); Goddess.setemail ("[Email protected]"); goddess.setaddress ("Beijing Red light District"); 30Dao.addgoddess (goddess); 31}32 33/**34 * Query Single goddess * * @param id37 * @return38 * @throws SQLException39 */40 public goddess get (Integer id) throwsSQLException41{Goddessdao DAO = newGoddessdao ();Dao.querybyid (ID); 44}45 46/**47 * Modified Goddess * * @param goddess50 * @throws Exception51 */52 public void edit (goddess Goddess) throwsException53{Goddessdao DAO = newGoddessdao (); 55Dao.updategoddess (goddess); 56}57 58/**59 * Delete Goddess * * * @param id62 * @throws SQLException63 */64 public void del (Integer id) throwsSQLException65 {Goddessdao dao = new Goddessdao (); dao.deletegoddess (ID); }69 70/**71 * Query All Goddesses * * * @return74 * @throws Exception75 */76 public list<goddess> query () throws Exception77 {Godd Essdao dao = new Goddessdao (); return dao.query (); }81 82/**83 * Test Success * * * @param args86 * @throws SQLException87 */88 public static void Main (string[] args) throws SQLException89 {Goddessdao Goddessdao = n EW Goddessdao (); list<goddess> goddesslist = goddessdao.query (); 94 for (goddess Goddess:god desslist) System.out.println (goddess.getname () + "," + Goddess.getmobie () + "," + Goddess.getemail ()); 97 }98 }99}                

Finally, let's take a look at the success of the main method's running result:

In this way, a simple java JDBC Connection MySQL database implementation additions and deletions are completed, you can try to do a query based on a high-level query, that is, multi-conditional query to consolidate the use of JDBC.

Now 21:00, has not eaten dinner, time is relatively tight, so did not give everyone to test the function of adding and removing, idle nothing to do the egg ache can all go to test, if found the problem, hope to be able to correct little Alan, little Alan has time to fix some errors in the blog post.

Finally, I wish you all can do a beautiful dream tonight, little Alan Eat supper, next time goodbye!

Java JDBC Connection MySQL database implementation additions and deletions