Simple addition, deletion, modification, and query using JDBC (take MySQL as an example), jdbcmysql

Simple addition, deletion, modification, and query using JDBC (take MySQL as an example), jdbcmysql

Directory

JDBC

I. Preparations (I): MySQL installation configuration and basic learning

2. Preparation (2): Download and import the jar package corresponding to the database

III. Basic JDBC operations

(1) define the record class (optional)

(2) connection acquisition

(3) insert

(4) update

(5) select

(6) delete

Iv. Test

V. Code Analysis

6. Thinking

 

 

JDBC

Wikipedia introduction:

　　Java Database Connectivity (JDBC) is an application interface used in Java to regulate how client programs access databases, provides methods such as querying and updating data in the database. JDBC is also the trademark of Sun Microsystems. JDBC is oriented to relational databases.

In short, it is a type of Java API used to execute SQL statements. through JDBC, we can directly use Java programming to operate on relational databases. Through encapsulation, developers can use pure Java APIs to execute SQL statements.

 

I. Preparations (I): MySQL installation configuration and basic learning

Before using JDBC to operate a database, you must first have a database. Here, we provide three links for readers to learn by themselves. If you have had any experience using SQL language (including classroom learning in school), the first two links are sufficient to get started.

1. installation and configuration: mysql installation illustration mysql graphic installation tutorial (details)

2. Basic operations: 21-minute MySQL getting started tutorial

3. simple command query: One thousand lines of MySQL Study Notes

 

　　It is recommended that you take a look at the Getting Started tutorial and practice, and create the tables to be used later while practicing basic operations such as insert, update, select, and delete.

Is the table of the database that I will use to demonstrate next.

　　　　

 

2. Preparation (2): Download and import the jar package corresponding to the database

To use JDBC, You need to import the corresponding jar package in the project. For the relationship between databases and JDBC packages, see jar packages, Driver Class names, and URL formats for various databases. Import method in Eclipse:

Right-click the project icon, select Properties, and select Add External JARs… in Java Bulid Path ...", Select the jar package downloaded and decompressed.

If you operate MySQL, the following import will not report an error:

import com.mysql.jdbc.Connection;import com.mysql.jdbc.PreparedStatement;

In addition, you also need to directly import the JDBC package.

import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;

 

III. Basic JDBC operations

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

　　Let's take a look at the specific code and practice. The fifth part of this article studies the APIs used.

All the following methods and data members are in the public class JDBCOperation.

(1) define the record class (optional)

This is required to facilitate operations and interface definition.

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) connection acquisition

You must obtain the connection to the database before the operation.

For the formats of driver and url, see the jar packages, driver Class names, and URL formats of various databases.

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 the 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 as an 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. Test

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

net start mysql

 

Test code

public static void main(String args[]) {    JDBCOperation.getAll();    JDBCOperation.insert(new Student("Achilles", "Male", "14"));    JDBCOperation.getAll();    JDBCOperation.update(new Student("Bean", "", "7"));    JDBCOperation.delete("Achilles");    JDBCOperation.getAll();}

 

Eclipse output

============================1    Ender        male    8    2    Bean        male    6    3    Petra        fema    9    4    Peter        male    9    5    _Graff        male    40    6    GOD        fema    255    ========================================================1    Ender        male    8    2    Bean        male    6    3    Petra        fema    9    4    Peter        male    9    5    _Graff        male    40    6    GOD        fema    255    7    Achilles    Male    14    ============================resutl: 1resutl: 1============================1    Ender        male    8    2    Bean        male    7    3    Petra        fema    9    4    Peter        male    9    5    _Graff        male    40    6    GOD        fema    255    ============================

 

V. Code Analysis

In the process of adding, deleting, modifying, and querying databases, we can find the common part, that is, the general process:

(1) create a Connection object and a SQL query command string;

(2) input an SQL query command to the Connection object to obtain the PreparedStatement object;

(3) execute executeUpdate () or executeQurey () on the PreparedStatement object to obtain the result;

(4) Close PreparedStatement objects and Connection objects successively.

It can be seen that when using JDBC, the two most common classes are Connection and PreparedStatement, And the ResultSet class in select. Read the Java API manual to learn its specific meaning and methods.

The following documents reference the Java API are from http://download.oracle.com/technetwork/java/javase/6/docs/zh/api/index.html.

　　

Connection

Java. SQL
Interface Connection
 
 

  
  
 
   All super interfaces: 
  
  
  

    Wrapper 
  
 
 
 
public interface Connectionextends Wrapper
 
Connection (session) to a specific database ). Execute the SQL statement in the connection context and return the result.
ConnectionThe object database can provide information about its tables, supported SQL syntax, stored procedures, and connection functions. This information is usedgetMetaDataMethod.

 

PreparedStatemnt

Java. SQL
Interface PreparedStatement
 
 

  
  
 
   All super interfaces: 
  
  
  

    Statement, Wrapper 
  
 
 
 
 

  
  
 
   All known sub-interfaces: 
  
  
  

    CallableStatement 
  
 
 
 
public interface PreparedStatementextends Statement
Indicates the objects of pre-compiled SQL statements.
SQL statements are pre-compiled and stored inPreparedStatementObject. This object can then be used to efficiently execute the statement multiple times.
 
Common Methods
Boolean execute ()
Execute an SQL statement in this PreparedStatement object. This statement can be any type of SQL statement.
ResultSet executeQuery ()
Execute an SQL query in this PreparedStatement object and return the ResultSet object generated by this query.
Int executeUpdate ()
Execute an SQL statement in this PreparedStatement object. The statement must be a Data Manipulation Language (DML) statement, such as an INSERT, UPDATE, or DELETE statement; or an SQL statement without returned content, such as a DDL statement.

　　

ResultSet

　　
Java. SQL
Interface ResultSet
 
 

  
  
 
   All super interfaces: 
  
  
  

    Wrapper 
  
 
 
 
 

  
  
 
   All known sub-interfaces: 
  
  
  

    CachedRowSet, FilteredRowSet, JdbcRowSet, JoinRowSet, RowSet, SyncResolver, WebRowSet 
  
 
 
 
public interface ResultSetextends Wrapper
The data table of the database result set. It is usually generated by executing a database query statement.

　　

6. Thinking

1. Each SQL operation requires establishing and disabling connections, which will inevitably consume a lot of resource overhead. How can we avoid this?

Analysis: You can use a connection pool to maintain connections in a unified manner without having to create or close them each time. In fact, this is used by many JDBC encapsulation methods.

 

2. In Java code, what should I do if the input data format is different from the database definition? For example, assign the String object of Java to the tinyint attribute of the database.

Analysis: the database tries to convert the SQL statement. According to my experiment, if the age attribute of tinyint is passed into a String object with a pure letter in content, it will be converted to 0. The specific conversion rules should be related to the database.

Who has the project for adding, deleting, modifying, and querying Mysql to connect to the database? jdbc simple javabean,

I sent it to you. I have an account on it. We are working on this project now. Haha! Come on, I hope you can successfully add, delete, and modify the difference ..

Kneeling, JDBC (MySQL) works with Spring's add, delete, modify, and query examples to directly import the project to Myeclipse for running.

Please refer to this article for details.
Reference: www.cnblogs.com/..c.html