Java JDBC Connection MySQL database implementation additions and deletions to check operation _java

JDBC believe that everyone is not unfamiliar, as long as it is a Java, first contact with the EE is to learn such things, who called the program and the database to deal with it! And JDBC is a database dealing with a very basic knowledge, but also relatively close to the bottom, in the actual work we use more in fact or more mature framework, such as Hibernate, MyBatis.

But as these mature framework of the bottom of the JDBC is also we should go to grasp, only to understand the JDBC and the deletion and change, so that in the future if interested to study hibernate or MyBatis source code can better to understand how these mature framework is to realize how to implement additions and deletions to check.

To get back to business, let's take a look at our development environment:

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

The installation and use of the development environment please consult your own information (very simple), not detailed here.

first step, create a database, use the NAVICAT Database visualization tool to create a database, set up a table in the library, a list of several fields (remember to give an ID field, unique primary key, self-added sequence), and then casually give two data will be good, used to test the function, as shown:

The second step is to get through the database (this example would like everyone to knock on their own, not much time, familiar with how JDBC and the database to deal with, so the diagram), as shown:

The third step, the transformation of the Dbutil class, to facilitate the DAO layer to obtain a database connection, the code is as follows:

 Package com.czgo.db;


Import java.sql.Connection;
Import Java.sql.DriverManager;
Import java.sql.SQLException;

public class Dbutil
{
  private static final String URL = "Jdbc:mysql://127.0.0.1:3306/imooc";
  private static final String UNAME = "root";
  private static final String PWD = "root";

  private static Connection conn = null;

  Static
  {
    try
    {
      //1. Load driver
      class.forname ("Com.mysql.jdbc.Driver");
      2. Access to the database connection
      conn = drivermanager.getconnection (URL, UNAME, PWD);
    }
    catch (ClassNotFoundException e)
    {
      e.printstacktrace ();
    }
    catch (SQLException e)
    {
      e.printstacktrace ();
    }
  }

  public static Connection getconnection ()
  {return
    conn;
  }
}

The fourth step, the creation of entity classes (as shown above, you see the allocation of the package, we will use MVC thought design This example, there are design ideas about MVC, please learn by yourselves, here is not much to say) the code is as follows:

Package Com.czgo.model;

Import java.io.Serializable; /** * Entity class: Goddess Class * * @author Alanlee * */public class Goddess implements Serializable {private static final long s

  Erialversionuid = 1L;
  /** * Unique PRIMARY KEY * * Private Integer ID;
  /** * Name/private String name;
  /** * Mobile Phone number * * Private String Mobie;
  /** * e-mail/private String email;

  /** * Home Address/private String addresses;
  Public Integer GetId () {return id;
  The public void SetId (Integer id) {this.id = ID;
  Public String GetName () {return name;
  public void SetName (String name) {this.name = name;
  Public String Getmobie () {return mobie;
  } public void Setmobie (String mobie) {This.mobie = Mobie;
  Public String Getemail () {return email;
  public void Setemail (String email) {this.email = email;
  Public String getaddress () {return address; } public void setaddress (String address) {this.address = address;

 }
}

step Fifth, DAO layer implementation (here because it's a small example without a DAO interface written, a large project in actual work should be written to the DAO interface to facilitate maintenance and extension of the program), the code is as follows:

Package Com.czgo.dao;
Import java.sql.Connection;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;
Import java.sql.SQLException;
Import java.util.ArrayList;

Import java.util.List;
Import Com.czgo.db.DBUtil;

Import com.czgo.model.Goddess; /** * Data Layer Processing class * * @author Alanlee * * */public class Goddessdao {/** * query all Goddess * * * @return * @throws S Qlexception */Public list<goddess> query () throws SQLException {list<goddess> goddesslist = new A

    Rraylist<goddess> ();

    Get database connection Connection conn = Dbutil.getconnection ();
    StringBuilder sb = new StringBuilder ();

    Sb.append ("Select id,name,mobie,email,address from Goddess");

    Through the database connection operation database, realize the deletion and change check preparedstatement ptmt = Conn.preparestatement (sb.tostring ());

    ResultSet rs = Ptmt.executequery ();

    Goddess Goddess = null;
      while (Rs.next ()) {goddess = new Goddess ();
      Goddess.setid (Rs.getint ("id")); Goddess.setname (Rs.getstring ("NAme "));
      Goddess.setmobie (rs.getstring ("Mobie"));
      Goddess.setemail (rs.getstring ("email"));

      Goddess.setaddress (rs.getstring ("Address"));
    Goddesslist.add (goddess);
  return goddesslist; /** * Query Single goddess * * @return * @throws SQLException/public goddess Querybyid (Integer id) throws Sqle

    xception {Goddess G = null;

    Connection conn = Dbutil.getconnection ();

    String sql = "" + "SELECT * from imooc_goddess" + "where id=?";

    PreparedStatement ptmt = conn.preparestatement (sql);

    Ptmt.setint (1, id);

    ResultSet rs = Ptmt.executequery ();
      while (Rs.next ()) {g = new Goddess ();
      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"));
  } return G; /** * Add Goddess * * @throws SQLException/public void addgoddess (goddess Goddess) throwsSQLException {//Get database connection Connection conn = Dbutil.getconnection ();

    String sql = "INSERT INTO Goddess (name,mobie,email,address) VALUES (?,?,?,?)";

    PreparedStatement ptmt = conn.preparestatement (sql);
    Ptmt.setstring (1, Goddess.getname ());
    Ptmt.setstring (2, Goddess.getmobie ());
    Ptmt.setstring (3, Goddess.getemail ());

    Ptmt.setstring (4, goddess.getaddress ());
  Ptmt.execute (); /** * Change Goddess Data * * @throws SQLException/public void updategoddess (goddess Goddess) throws Sqlexceptio

    n {Connection conn = dbutil.getconnection (); String sql = "Update goddess set name=?,mobie=?,email=?,address=?"

    where id=? ";

    PreparedStatement ptmt = conn.preparestatement (sql);
    Ptmt.setstring (1, Goddess.getname ());
    Ptmt.setstring (2, Goddess.getmobie ());
    Ptmt.setstring (3, Goddess.getemail ());

    Ptmt.setstring (4, goddess.getaddress ());
  Ptmt.execute (); /** * Delete Goddess * * @throws SQLException/public voidDeletegoddess (Integer id) throws SQLException {Connection conn = dbutil.getconnection ();

    String sql = "Delete from goddess where id=?";

    PreparedStatement ptmt = conn.preparestatement (sql);

    Ptmt.setint (1, id);
  Ptmt.execute ();

 }
}

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

Package com.czgo.action;
Import java.sql.SQLException;

Import java.util.List;
Import Com.czgo.dao.GoddessDao;

Import com.czgo.model.Goddess;
   /** * Control layer, directly in the construction of data, interface data is received through the request, but also the same * * @author alanlee * */public class Goddessaction {/** * add goddess  * * @param goddess * @throws Exception/public void Add (Goddess Goddess) throws Exception {Goddessdao
    DAO = new Goddessdao ();
    Goddess.setname ("Cang jing Empty");
    Goddess.setmobie ("52220000");
    Goddess.setemail ("52220000@qq.com");
    Goddess.setaddress ("Beijing Red Light District");
  Dao.addgoddess (goddess); /** * Query Single goddess * * @param ID * @return * @throws SQLException/public goddess get (Integer id) th
    Rows SQLException {Goddessdao dao = new Goddessdao ();
  return Dao.querybyid (ID); /** * Modification Goddess * * @param goddess * @throws Exception * * public void edit (goddess Goddess) throws Excep
    tion {Goddessdao dao = new Goddessdao ();
  Dao.updategoddess (goddess);/** * Delete Goddess * * @param ID * @throws SQLException/public void del (Integer id) throws SQLException
    {Goddessdao dao = new Goddessdao ();
  Dao.deletegoddess (ID); 
  /** * Query All Goddess * * @return * @throws Exception/Public list<goddess> query () throws Exception
    {Goddessdao dao = new Goddessdao ();
  return Dao.query (); /** * Test Success * * @param args * @throws SQLException/public static void main (string[] args) throw

    s SQLException {Goddessdao Goddessdao = new Goddessdao ();

    list<goddess> goddesslist = Goddessdao.query (); for (goddess Goddess:goddesslist) {System.out.println (Goddess.getname () + "," + Goddess.getmobie () + "," + Go
    Ddess.getemail ());

 }
  }
}

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

In this way, a simple java JDBC Connection MySQL database implementation additions and deletions are completed, we can try to do a query on the basis of an advanced query, that is, multiple conditional query to consolidate the use of JDBC.

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.