Google's younger brother learning backstage (--JDBC)

Source: Internet
Author: User
Tags sql injection

Explore the puzzles of Android soft keyboard
Delve into Android Async Essentials Handler
The essential cornerstone of Android's mainstream framework
Standing on the shoulder of the source code full solution scroller working mechanism

Android multi-resolution adaptation framework (1)-Core Foundation
Android multi-resolution adaptation framework (2)-Principle analysis
Android multi-resolution adaptation framework (3)-User Guide

Custom View Series Tutorial 00– Overthrow yourself and the past, re-learn custom view
Custom View Series Tutorials 01– Common Tools Introduction
Custom View Series Tutorial 02–onmeasure Source detailed analysis
Custom View Series Tutorial 03–onlayout Source detailed analysis
Custom View Series Tutorial 04–draw source code Analysis and its practice
Custom View Series Tutorial 05– Sample Analysis
Custom View Series Tutorial 06– Detailed View Touch event handling
Custom View Series Tutorial 07– ViewGroup distributing Touch events
Custom View Series Tutorial 08– the generation and processing of sliding conflicts

Copyright Notice
    • This article original Google's younger brother
    • Author Blog address: HTTP://BLOG.CSDN.NET/LFDFHL
Introduction to this article

This article mainly introduces the basic knowledge of JDBC and its usage.

Introduction to JDBC

JDBC (Java Database Connectivity) is a set of standard specifications that Sun offers to manipulate databases using the Java language.
Many interfaces are defined in JDBC and database vendors implement these interfaces in their database drivers. For a JDBC-to-database-driven relationship, see:

The interfaces (specifications) defined in JDBC, which are implemented by database vendors (such as MYSQL,ORACLE,DB2, etc.) in their database drivers. So, JDBC masks the details of Java's dealings with different databases, and if the project was originally using MySQL and replaced it with Oracle after a certain period of time, then we basically didn't have to modify the SQL statements in the original project. Just switch the MySQL database driver in JDBC to the database driver for Oracle.

Core technical points of JDBC:

    • DriverManager: for loading drives
    • Connection: Used to create a connection to a database
    • Statement: For manipulating SQL statements
    • ResultSet: Result set or a virtual table

The steps of the JDBC operation:

    • Load Driver
    • Create a connection
    • Create a statement object
    • Executes the SQL statement and returns the result
    • Processing results
    • Close Resource

Well, after understanding this, let's look at a simple example with the following code:

 Packagecn.com;ImportJava.sql.Connection;ImportJava.sql.DriverManager;ImportJava.sql.PreparedStatement;ImportJava.sql.ResultSet;ImportJava.sql.SQLException;ImportJava.sql.Statement;ImportOrg.junit.Test;/** * Original Google's younger brother * Blog address: HTTP://BLOG.CSDN.NET/LFDFHL * * Public  class unittest {    @Test     Public void testJDBC1() {Connection Connection =NULL; Statement Statement =NULL; ResultSet ResultSet =NULL;Try{Class.forName ("Com.mysql.jdbc.Driver"); Connection = Drivermanager.getconnection ("JDBC:MYSQL://LOCALHOST:3306/DB1","Root","Root");            statement = Connection.createstatement (); String sql ="SELECT * FROM Student"; ResultSet = statement.executequery (sql); while(Resultset.next ()) {Student Student =NewStudent ();intid = resultset.getint ("StudentID"); String name = Resultset.getstring ("Studentname");                Student.setstudentid (ID);                Student.setstudentname (name);            SYSTEM.OUT.PRINTLN (student); }        }Catch(Exception e)        {E.printstacktrace (); }finally{if(ResultSet! =NULL) {Try{Resultset.close (); }Catch(SQLException e)                {E.printstacktrace (); } ResultSet =NULL; }if(Statement! =NULL) {Try{Statement.close (); }Catch(SQLException e)                {E.printstacktrace (); } statement =NULL; }if(Connection! =NULL) {Try{Connection.close (); }Catch(SQLException e)                {E.printstacktrace (); } connection =NULL; }        }    }}

In this example, you use JDBC to query data from the database. The code resolves as follows:

    • The first step:
      Load MySQL database driver, see Code line 22nd
    • Step Two:
      To create a connection to the data, see Line 23rd of the code
    • Step Three:
      To create a statement object to execute the SQL statement, see Line 24th of the Code
    • Fourth Step:
      Using statement to execute the SQL statement and get the result set, see Line 26th of the Code
    • Fifth Step:
      Working with result sets, see Code 第27-34 lines
    • Sixth step:
      To close a resource, see Code 第37-64 Line
detailed JDBC Common classes and Interfaces

Using the example above, we have a preliminary understanding and cognition of the use of JDBC, and now we will explain the common classes and interfaces of JDBC on this basis.

Java.sql.Drivermanager class

The Java.sql.Drivermanager class is used to load (register) the drive and establish a connection to the database.

    • Use the Java.sql.Drivermanager class to load (register) the driver

      Here, we are using:

      Class.forName ("Com.mysql.jdbc.Driver");

      There is another way:

      Drivermanager.registerdriver (New Com.mysql.jdbc.Driver ());

      This approach not only strongly relies on the drive jar of the database but also causes the driver to be re-registered 2 times. It is therefore not recommended to use this method

    • Using the Java.sql.Drivermanager class to establish a connection to a database

      For example, in this sample:

      Drivermanager.getconnection ("Jdbc:mysql://localhost:3306/db1", "root", "root");

      where JDBC represents the protocol, MySQL represents the sub-protocol, localhost represents the port number for ip,3306, DB1 represents the database to connect to, the first root represents the user name, and the second root represents the password. These values are the configurations in my example, and replace these values when used in a project.

Java.sql.Connection Interface

Java.sql.Connection is an interface that is used to interact with a database, and that interface is implemented in each database driver. The most common operation for Java.sql.Connection is to create a statement for executing SQL statements, namely:

Connection.createstatement ();

Java.sql.Statement Interface

The Java.sql.Statement interface is used to manipulate SQL statements and return corresponding result sets, which are implemented in each database driver. The common methods of Java.sql.Statement are as follows:

    • ExecuteQuery (String sql)
      This method is used to execute a SELECT statement and return a result set based on a query statement
    • Executeupdate (String sql)
      This method is used to execute an INSERT, UPDATE, DELETE statement and return the number of rows affected
    • Execute (String sql)
      This method can execute arbitrary SQL statements. The method returns true if and only if the SELECT statement is executed and there is a return result, in other cases the methods return False
Java.sql.ResultSet Interface

Java.sql.ResultSet is used to save the result set obtained after executing the SQL statement, and the interface is implemented in each database driver.

ResultSet exposes a cursor, which by default points to the first row of the result set. ResultSet common methods are interpreted as follows:

Boolean Next ()

Moves a cursor down one line from its current position

Boolean Previous ()

Moves the cursor to the previous line of this ResultSet object.

void Afterlast ()

Move the cursor to the end, just after the last line.

void Beforefirst ()

Move the cursor to the beginning, just before the first line.

Object getObject (int columnindex);

Based on ordinal value, index starting from 1

Object GetObject (String colomnname);

Value based on column name

int getInt (int colindex)

Gets the current row of the resultset result set as an int specifies the column number value

int getInt (String collabel)

Get resultset result set current row specified column name value as int

float getfloat (int colindex)

Get resultset result set current row specified column number value in float form

Float getfloat (String collabel)

Get resultset result set current row specified column name value in float form

String getString (int colindex)

Gets the current row of the resultset result set as a string specifying the column number value

String getString (String Collabel)

Gets the current row of the resultset result set in string form the specified column name value

Date getDate (int columnindex);

Get resultset result set current row specified column number value as Date

Date getDate (String columnName);

Get resultset result set current row specified column number value as Date

void Close ()

Close the ResultSet object

After we get the data from the resultset into the database, we encapsulate the data into the JavaBean, and notice the correspondence between the Java data type and the data type of the database:

using PreparedStatement to prevent SQL injection

PreparedStatement (Precompiled Object) is a subclass of statement, which has the following characteristics:

    • Pre-compiling SQL statements
    • Excellent performance
    • Using placeholders in SQL statements

Here is an example of using PreparedStatement to optimize the code as follows:

 Packagecn.com;ImportJava.sql.Connection;ImportJava.sql.DriverManager;ImportJava.sql.PreparedStatement;ImportJava.sql.ResultSet;ImportJava.sql.SQLException;ImportJava.sql.Statement;ImportOrg.junit.Test;/** * Original Google's younger brother * Blog address: HTTP://BLOG.CSDN.NET/LFDFHL * * Public  class unittest {    @Test     Public void testJDBC2() {Connection Connection =NULL; PreparedStatement PreparedStatement =NULL; ResultSet ResultSet =NULL;Try{Class.forName ("Com.mysql.jdbc.Driver"); Connection = Drivermanager.getconnection ("JDBC:MYSQL://LOCALHOST:3306/DB1","Root","Root"); String sql ="SELECT * from student where studentid=?" and studentname=? ";            PreparedStatement = connection.preparestatement (sql); Preparedstatement.setint (1,4); Preparedstatement.setstring (2,"Bo Shao-ye knot clothing"); ResultSet = Preparedstatement.executequery (); while(Resultset.next ()) {Student Student =NewStudent ();intid = resultset.getint ("StudentID"); String name = Resultset.getstring ("Studentname");                Student.setstudentid (ID);                Student.setstudentname (name);            SYSTEM.OUT.PRINTLN (student); }        }Catch(Exception e)        {E.printstacktrace (); }finally{if(ResultSet! =NULL) {Try{Resultset.close (); }Catch(SQLException e)                {E.printstacktrace (); } ResultSet =NULL; }if(PreparedStatement! =NULL) {Try{Preparedstatement.close (); }Catch(SQLException e)                {E.printstacktrace (); } PreparedStatement =NULL; }if(Connection! =NULL) {Try{Connection.close (); }Catch(SQLException e)                {E.printstacktrace (); } connection =NULL; }        }    }}

At this point, the basic knowledge of JDBC is finished, thank you.

Google's younger brother learning backstage (--JDBC)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.