Simple additions and deletions via JDBC (take MySQL for example)

Simple additions and deletions via JDBC (take MySQL for example)

Directory

Preface: What is JDBC

I. Preparatory work (i): MySQL installation configuration and basic Learning

Ii. preparatory Work (ii): Download the jar package for the database and import it

Third, JDBC Basic operation

(1) Define the class of the record (optional)

(2) access to the connection

(3) Insert

(4) Update

(5) Select

(6) Delete

Iv. Testing

Five, Code Analysis

Six, the question of thinking

Preface: What is JDBC

Introduction to Wikipedia:

　　A Java database connection, (Java db Connectivity, or JDBC) is an application interface in the Java language that regulates how a client program accesses a database, providing methods such as querying and updating data in a database. JDBC is also the trademark of Sun Microsystems. It is JDBC-oriented for relational databases.

Simply put, this is the Java API used to execute SQL statements, which allows us to manipulate the relational database directly using Java programming. Encapsulation allows developers to complete SQL execution using a pure Java API.

I. Preparatory work (i): MySQL installation configuration and basic Learning

Before using JDBC to manipulate the database, you first need to have a database. Here are 3 links for self-learning, and if you've ever had a SQL language experience (including classroom learning in school), the top two links are enough to get started.

1. Installation and configuration: MySQL installation diagram MySQL graphics installation tutorial (detailed description)

2. Basic operation: 21 minutes MySQL Getting Started tutorial

3. Simple command query: 1000 line MySQL study notes

　　It is recommended to look at the introductory tutorial, while practicing, in the practice of INSERT, UPDATE, select, delete and other basic operations, the following table to be used to build.

Is the table of the database that I'm going to use to demonstrate.

　　　　

Ii. preparatory Work (ii): Download the jar package for the database and import it

Using JDBC requires that the corresponding jar package be imported into the project. The corresponding relationship between the database and the JDBC package can refer to the jar package, driver class name and URL format for each database. How to import under Eclipse:

Right-click on the project's icon, select "Properties", select "Add External JARs ..." in "Java Bulid Path" and select the jar package that was obtained after downloading and extracting.

If you operate on MySQL, then the following import will not error:

Import Com.mysql.jdbc.connection;import com.mysql.jdbc.PreparedStatement;

In addition, you need a JDBC package, directly import.

Import Java.sql.drivermanager;import Java.sql.resultset;import java.sql.SQLException;

Third, JDBC Basic operation

For the sake of simplicity, database-related operations, commands, and parameters are hard-coded. Interested readers can explore these and reduce the coupling between data and operations.

　　First look at the specific code and practice, the fifth part of this article on the use of the API a little research.

All of the following methods and data members are inside the public class jdbcoperation.

(1) Define the class of the record (optional)

This is done primarily for ease of operation and interface definition, which is non-essential.

Static class Student {        private String Id;        Private String Name;        Private String Sex;        Private String age;        Student (String Name, String Sex, String age) {This            . Id = null; Default this            . name = name;            This. sex = sex;            This. Age = Age;        }        Public String getId () {            return Id;        }        public void SetId (String Id) {this            . id = ID;        }        Public String GetName () {            return Name;        }        public void SetName (String Name) {this            . name = name;        }        Public String Getsex () {            return Sex;        }        public void Setsex (String Sex) {this            . sex = sex;        }        Public String Getage () {            return age;        }        public void Setage (String age) {this            . Age = Age;        }}

(2) access to the connection

The connection to the database must be obtained before the operation.

Driver, URL format can also refer to various database corresponding jar package, driver class name and URL format.

private static Connection Getconn () {    String driver = "Com.mysql.jdbc.Driver";    String url = "jdbc:mysql://localhost:3306/samp_db";    String username = "root";    String password = "";    Connection conn = null;    try {        class.forname (driver);//classloader, load corresponding driver        conn = (Connection) drivermanager.getconnection (URL, Username, password);    } catch (ClassNotFoundException e) {        e.printstacktrace ();    } catch (SQLException e) {        e.printstacktrace () ;    }    Return conn;}

(3) Insert

private static int Insert (Student Student) {    Connection conn = Getconn ();    int i = 0;    String sql = "INSERT into students (Name,sex,age) VALUES (?,?,?)";    PreparedStatement pstmt;    try {        pstmt = (preparedstatement) conn.preparestatement (SQL);        Pstmt.setstring (1, Student.getname ());        Pstmt.setstring (2, Student.getsex ());        Pstmt.setstring (3, Student.getage ());        i = Pstmt.executeupdate ();        Pstmt.close ();        Conn.close ();    } catch (SQLException e) {        e.printstacktrace ();    }    return i;}

(4) Update

private static int update (Student Student) {    Connection conn = Getconn ();    int i = 0;    String sql = "Update students set age= '" + student.getage () + "' Where name= '" + student.getname () + "'";    PreparedStatement pstmt;    try {        pstmt = (preparedstatement) conn.preparestatement (SQL);        i = Pstmt.executeupdate ();        System.out.println ("Resutl:" + i);        Pstmt.close ();        Conn.close ();    } catch (SQLException e) {        e.printstacktrace ();    }    return i;}

(5) Select

Take select * from XXX for example.

private static Integer GetAll () {    Connection conn = Getconn ();    String sql = "SELECT * from students";    PreparedStatement pstmt;    try {        pstmt = (preparedstatement) conn.preparestatement (SQL);        ResultSet rs = Pstmt.executequery ();        int col = Rs.getmetadata (). getColumnCount ();
        System.out.println ("============================");        while (Rs.next ()) {for            (int i = 1; I <= col; i++) {                System.out.print (rs.getstring (i) + "\ t");                if ((i = = 2) && (rs.getstring (i). Length () < 8)) {                    System.out.print ("\ t");}             }            System.out.println ("");        }            System.out.println ("============================");    } catch (SQLException e) {        e.printstacktrace ();    }    return null;}

(6) Delete

private static int Delete (String name) {    Connection conn = Getconn ();    int i = 0;    String sql = "Delete from students where name= '" + Name + "'";    PreparedStatement pstmt;    try {        pstmt = (preparedstatement) conn.preparestatement (SQL);        i = Pstmt.executeupdate ();        System.out.println ("Resutl:" + i);        Pstmt.close ();        Conn.close ();    } catch (SQLException e) {        e.printstacktrace ();    }    return i;}

Iv. Testing

Before testing, you need to open the corresponding database service in the system. The startup command for MySQL under Windows is

net start MySQL

Test code

public static void Main (String args[]) {    jdbcoperation.getall ();    Jdbcoperation.insert (New Student ("Achilles", "Male", ")");    Jdbcoperation.getall ();    Jdbcoperation.update (New Student ("Bean", "" "," 7 "));    Jdbcoperation.delete ("Achilles");    Jdbcoperation.getall ();}

Output in Eclipse

============================1    Ender        male    8    2    Bean        male    6    3    Petra        FEMA    9    4    Peter        male    9    5    _graff        male    6    GOD        FEMA    255    ========================================================1    Ender        male    8    2    Bean        male    6    3    Petra        FEMA    9    4    Peter        male    9    5    _graff        male    6    GOD        FEMA    255    7    Achilles    male    ====== ======================resutl:1resutl:1============================1    Ender        male    8    2    Bean        male    7    3    Petra        FEMA    9    4    Peter        male    9    5    _graff        male    6    GOD        FEMA    255    ============================

Five, Code Analysis

In the process of the above-mentioned database, it can be found that the common part, namely the general process:

(1) Create connection object, SQL query command string;

(2) The connection object is passed into the SQL query command to obtain the PreparedStatement object;

(3) executeupdate () or Executequrey () to the PreparedStatement object to obtain the results;

(4) Close the PreparedStatement object and the connection object successively.

As you can see, when using JDBC, the most common dealings are the two classes of connection, PreparedStatement, and the ResultSet class in select. Check out the Java API manual to see its specific meaning and approach.

The data from the Java API referenced below is derived from http://download.oracle.com/technetwork/java/javase/6/docs/zh/api/index.html.

　　

Connection

java.sql
Interface Connection

All Super Interfaces:

Wrapper

Connectionextends Wrapper

A connection to a specific database (session). Executes the SQL statement in the connection context and returns the result.

ConnectionThe database of an object can provide information describing its tables, supported SQL syntax, stored procedures, this connection capability, and so on. This information is getMetaData obtained using the method.

Preparedstatemnt

java.sql
Interface PreparedStatement

All Super Interfaces:

Statement, Wrapper

All known sub-interfaces:

CallableStatement

PreparedStatementextends Statement

An object that represents a precompiled SQL statement.

The SQL statement is precompiled and stored in the PreparedStatement object. You can then use this object to execute the statement efficiently several times.

Common methods

Boolean Execute ()

Executes the SQL statement in this PreparedStatement object, which can be any kind of SQL statement.

ResultSet ExecuteQuery ()

Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.

int executeupdate ()

Executes the SQL statement in this PreparedStatement object, which must be a SQL data manipulation language (manipulation LANGUAGE,DML) statement, such as an INSERT, UPDATE, or DELETE statement, or a non-return SQL statements that return content, such as DDL statements.

　　

ResultSet

　　

java.sql
Interface ResultSet

All Super Interfaces:

Wrapper

All known sub-interfaces:

CachedRowSet, Filteredrowset, Jdbcrowset, Joinrowset, RowSet, Syncresolver, Webrowset

ResultSetextends Wrapper

A data table that represents a database result set, typically generated by executing statements that query the database.

　　

Six, the question of thinking

1. Each SQL operation needs to establish and close the connection, which is bound to consume a lot of resource overhead, how to avoid?

Analysis: You can use a connection pool to maintain a unified connection without having to build and close each time. In fact, this is a lot of the tools used to encapsulate JDBC.

In 2.Java code, what happens if the incoming data format is different from the database definition? If you assign a Java string object to the tinyint property of the database.

Analysis: The database attempts to convert when the SQL statement is executed. According to my experiment, if a string object with the contents of a pure letter is passed into the age property of tinyint, it will be converted to 0. The specific conversion rules should be related to the database.

Simple additions and deletions via JDBC (take MySQL for example)