Java database programming jdbc additions and deletions

Preface

Many ORM frameworks allow us to get rid of the native JDBC code, but at the beginning of the Learning database programming, we should master the underlying JDBC programming, skilled application, learn the framework will be more handy. Just like Jin Yong's martial arts characters, as long as the internal strength deep, and then to learn other martial arts unique knowledge, but is only moves. As the so-called, Wen know the new, this paper reviews the most basic JDBC additions and deletions to check the operation. 　　

connecting to a database

In a previous essay on how to connect to a database-the JDBC configuration of Java database programming.

Database connection Tool Class DBConnection

Package Database;import java.sql.*;import java.sql.statement;/** * Created by Michael Wong on 2015/11/28. */public class DBConnection {/** database address Jdbc:mysql://[ip]:[port]/[db_name] */private static final String URL = "Jdb    C:mysql://localhost:3306/test ";    /** User name */private static final String USERNAME = "root";    /** Password */private static final String PASSWORD = "root";    /** Drive class */private static final String DRIVER = "Com.mysql.jdbc.Driver";        Register the drive class static {try {class.forname (DRIVER);        } catch (ClassNotFoundException e) {e.printstacktrace (); }}/** * Get database connection * @return Connection * @throws SQLException * */public static Connection Getconn    Ection () throws SQLException {return drivermanager.getconnection (URL, USERNAME, PASSWORD); }/** * Close Related resources * @param connection Connection * @param statement statement * @param resultSet result set */public STA tic void Close (Connection connectIon, Statement Statement, ResultSet ResultSet) {try {if (connection! = null) {Connectio            N.close ();            } if (statement! = NULL) {statement.close ();            } if (ResultSet! = null) {resultset.close ();        }} catch (SQLException e) {e.printstacktrace (); }}/** * Close related resource overloads * @param connection Connection * @param statement statement */public static void Close (Conn    ection connection, Statement Statement) {Close (connection, Statement, null); }}

Database Build Table

Create a simple user table in the database that contains only two fields--id and name.                                                                                                                                Structures such as:  

Create a model

　　New User Class

Package database;/** * Created by Michael Wong on 2015/11/28. */public class User {    private int id;    private String name;    public User (int ID, String name) {        this.id = ID;        this.name = name;    }    Public User (String name) {        this.name = name;    }    public void setId (int id) {        this.id = ID;    }    public int getId () {        return this.id;    }    public void SetName (String name) {        this.name = name;    }    Public String GetName () {        return this.name;    }    @Override public    String toString () {        return "#" + ID + "Name:" + Name;}    }

additions and deletions to check and change

The JDBC operations database mainly uses connection, statement, and ResultSet related classes, which can be viewed by the official API.

. Connection-Connection to the specified database

. Statement-Executes the specific SQL statement

. ResultSet-Query Results

Package Database;import Java.sql.*;import Java.util.arraylist;import java.util.linkedlist;import java.util.List;/** * Created by Michael Wong on 2015/11/28.    */public class Userdao {/** database connection */private Connection Connection;        Public Userdao (Connection Connection) {this.connection = Connection;        try {Connection.setautocommit (true);        } catch (SQLException e) {e.printstacktrace ();  }}/** * Add a user * @param name username */public void AddUser (string name) {String sql = "INSERT        into user VALUES (?) ";        PreparedStatement preparedstatement = null;            try {preparedstatement = connection.preparestatement (sql);            Preparedstatement.setstring (1, name);        Preparedstatement.executeupdate ();        } catch (SQLException e) {e.printstacktrace ();        } finally {Dbconnection.close (connection, PreparedStatement);   }}/** * Delete one user  * @param name to delete user name */public void Removeuser (string name) {String sql = "Delete from user WHERE name =        ?";        PreparedStatement preparedstatement = null;            try {preparedstatement = connection.preparestatement (sql);            Preparedstatement.setstring (1, name);        Preparedstatement.executeupdate ();        } catch (SQLException e) {e.printstacktrace ();        } finally {Dbconnection.close (connection, PreparedStatement); }}/** * User name * @param ID ID * @return username */public String Getnamebyid (int id) {St        Ring sql = "SELECT name from user WHERE id =?";        PreparedStatement preparedstatement = null;        ResultSet ResultSet = null;        String name = NULL;            try {preparedstatement = connection.preparestatement (sql);            Preparedstatement.setint (1, id);            ResultSet = Preparedstatement.executequery ();     Resultset.next ();       Name = resultset.getstring (1);        } catch (SQLException e) {e.printstacktrace ();        } finally {Dbconnection.close (connection, PreparedStatement, ResultSet);    } return name; }/** * Modify a user name * @param ID User ID * @param name User modified name */public void updateUser (int id, String nam e) {String sql = "UPDATE user set name =?")        WHERE id =? ";        PreparedStatement preparedstatement = null;            try {preparedstatement = connection.preparestatement (sql);            Preparedstatement.setstring (1, name);            Preparedstatement.setint (2, id);        Preparedstatement.executeupdate ();        } catch (SQLException e) {e.printstacktrace ();        } finally {Dbconnection.close (connection, PreparedStatement); }}/** * Find all Users * @return User */Public list<user> getAllUsers () {String sql = "Select        * from user "; PreparedStatement pReparedstatement = null;        ResultSet ResultSet = null;        List<user> userlist = null;            try {preparedstatement = connection.preparestatement (sql);            ResultSet = Preparedstatement.executequery ();            userlist = new linkedlist<user> ();                while (Resultset.next ()) {int id = resultset.getint ("id");                String name = resultset.getstring ("name");            Userlist.add (new User (ID, name));        }} catch (SQLException e) {e.printstacktrace ();        } finally {Dbconnection.close (connection, PreparedStatement, ResultSet);        } list<user> returnedlist = new arraylist<user> ();        Returnedlist.addall (userlist);    return returnedlist; }}

The preparedstatement--Prep statement is used here. PreparedStatement is precompiled with respect to statement, which greatly increases efficiency for batch processing, and statement executes SQL statements every time the SQL statement is executed. If you only do one-time access to the database, use statement processing more efficiently. The PREP statement improves query performance, and whenever a database executes a query, it always evaluates the query policy first to efficiently perform the query operation. By preparing the query in advance and reusing it multiple times, you can ensure that the preparation steps required for the query are executed only once.

In the preliminary query statement, each host variable has a "? "To express. If more than one variable exists, you must be careful when setting the value of the variable. "Location. Before you execute a prestaged statement, you must bind the variable to the actual value using the Set method. Similar to the Get method in the ResultSet method, there are different set methods for different data types.

Note : The iteration method of the ResultSet class is slightly different from the Java.util.Iterator interface. For the ResultSet class, when the iterator is initialized, it is positioned before the first line, and the next method must be called to move it to the first row. In addition, it has no hasnext method, we need to call next continuously until the method returns false.

Java database programming jdbc additions and deletions