"Java Programming" establishes a simple JDBC connection-drivers, Connection, Statement and PreparedStatement

This blog provides a simple JDBC connection routine through the JDBC driver, and realizes the query to the database through statement and PreparedStatement respectively. The next blog will focus on the differences between statement and PreparedStatement.

1. Add the JDBC driver for the project

1) JDBC Driver download

Official: Mysql-connector-java-5.0.8.zip

CSDN Information: Mysql-connector-java-5.0.8.zip

2) Add the JDBC driver for the project

Build the Project Java Project Jdbcdemo, and set up a Lib folder in the Jdbcdemo project, copy the drive file to the Lib folder, select the driver file, right-->buildpath->add to build Path;:

2. Establish Db_bbs Database

1) build a database Db_bbs;

2) Execute the SQL statement of the Db_bbs.sql file, create the user table in the Db_bbs database, and add the data;

SET foreign_key_checks=0;--------------------------------Table structure for ' user '------------------------------ DROP TABLE IF EXISTS ' user '; CREATE TABLE ' user ' (  ' id ' int (one) ' is not null auto_increment,  ' username ' varchar () is not null,  ' password ' varch  AR (TEN) not NULL,  ' gender ' varchar (1) is not NULL,  ' Regtime ' datetime is NOT NULL,  PRIMARY KEY (' id ')) engine=innodb auto_increment=4 DEFAULT charset=gb2312;--------------------------------Records of user--------------------------- ---insert INTO ' user ' values (' 1 ', ' Andy ', ' Andy ', ' 1 ', ' 2014-05-13 17:33:28 '); insert into ' user ' values (' 2 ', ' Jack ', ' ja CK ', ' 1 ', ' 2014-05-14 17:33:55 '); INSERT into ' user ' VALUES (' 3 ', ' Rose ', ' Rose ', ' 0 ', ' 2014-05-13 17:34:36 ');

3. Configuring the database through the properties file

1) attribute profile db.properties;

#mysql DB propertiesdb_driver_class=com.mysql.jdbc.driverdb_url=jdbc:mysql://localhost:3306/db_bbsdb_username= Rootdb_password=root #Oracle DB properties#db_driver_class=oracle.jdbc.driver.oracledriver#db_url=jdbc:oracle: Thin: @localhost: 1571:db_bbs#db_username=scott#db_password=tiger

2) Add the property profile to the project's root directory;

4. Establishing a JDBC Connection

Package Com.andieguo.jdbc;import Java.io.fileinputstream;import Java.io.ioexception;import java.sql.Connection; Import Java.sql.drivermanager;import Java.sql.preparedstatement;import Java.sql.resultset;import Java.sql.sqlexception;import Java.sql.statement;import java.util.properties;/** * This class encapsulates the connection and shutdown database connection operations * * @author Andieguo * */public class DBConnection {public static Connection getconnection () {Properties props = new Properties (); FileInputStream FIS = null; Connection con = null;try {fis = new FileInputStream ("Db.properties");p rops.load (FIS);//Load Driver Class.forName ( Props.getproperty ("Db_driver_class"));//Create a connection con = drivermanager.getconnection (Props.getproperty ("DB_URL"), Props.getproperty ("Db_username"), Props.getproperty ("Db_password"));} catch (IOException | SQLException | ClassNotFoundException e) {e.printstacktrace ();} return con;} Close resultsetpublic static void Closeresultset (ResultSet rs) {if (rs! = null) {try {rs.close (); rs = null;} catch (Sqlexce Ption e) {e.printstacktrace ();}}} Close statementpublic static void Closestatement (Statement stm) {if (STM! = null) {try {stm.close (); STM = null;} catch (SQL Exception e) {e.printstacktrace ();}}} Close preparedstatementpublic static void Closepreparedstatement (PreparedStatement pstm) {if (pstm! = null) {try {Pstm.clo SE ();p stm = null;} catch (SQLException e) {e.printstacktrace ();}}} Close connectionpublic static void CloseConnection (Connection con) {if (con! = null) {try {con.close (); con = null;} catch ( SQLException e) {e.printstacktrace ();} con = null;}}}

5. Use statement to query

Package Com.andieguo.jdbc;import Java.sql.connection;import Java.sql.resultset;import java.sql.SQLException;import Java.sql.statement;import Java.util.arraylist;import Java.util.list;public class DBHelper {public static void Queryallbystatement () {list<user> users = new arraylist<user> (); Connection conn = Dbconnection.getconnection (); Statement stmt = null; ResultSet rs = null;try {stmt = Conn.createstatement (), rs = Stmt.executequery ("SELECT * from User"), while (Rs.next ())} {use R user = new User (), User.setid (Rs.getint ("id")), User.setusername (rs.getstring ("username")), User.setpassword ( Rs.getstring ("password")), User.setgender (Rs.getboolean ("Gender")), User.setregtime (Rs.getdate ("Regtime")); System.out.println (User.tostring ()); Users.add (user);}} catch (SQLException e) {e.printstacktrace ();} finally {Dbconnection.closeresultset (RS);D bconnection.closestatement ( stmt);D bconnection.closeconnection (conn);}} public static void Querybyid (Integer id) {Connection conn = Dbconnection.getconnection (); Statement stmt = null;  ResultSet rs = null;try {stmt = Conn.createstatement (); rs = Stmt.executequery ("SELECT * from user where id =" + ID); (Rs.next ()) {User user = new user (), User.setid (Rs.getint ("id")), User.setusername (rs.getstring ("username")); User.setpassword ( Rs.getstring ("password")), User.setgender (Rs.getboolean ("Gender")), User.setregtime (Rs.getdate ("Regtime")); System.out.println (User.tostring ());}} catch (SQLException e) {e.printstacktrace ();} finally {Dbconnection.closeresultset (RS);D bconnection.closestatement ( stmt);D bconnection.closeconnection (conn);}}

6. Use PreparedStatement to query

Package Com.andieguo.jdbc;import Java.sql.connection;import Java.sql.preparedstatement;import java.sql.ResultSet; Import Java.sql.sqlexception;import Java.util.arraylist;import Java.util.list;public class DBHelper {public static void Queryallbypreparestatement () {list<user> users = new arraylist<user> (); Connection conn = Dbconnection.getconnection (); PreparedStatement PS = null; ResultSet rs = null;try {PS = conn.preparestatement ("SELECT * from User"), rs = Ps.executequery (); while (Rs.next ()) {User U Ser = new User (), User.setid (Rs.getint ("id")), User.setusername (rs.getstring ("username")), User.setpassword ( Rs.getstring ("password")), User.setgender (Rs.getboolean ("Gender")), User.setregtime (Rs.getdate ("Regtime")); System.out.println (User.tostring ()); Users.add (user);}} catch (SQLException e) {e.printstacktrace ();} finally {Dbconnection.closeresultset (RS);D bconnection.closestatement ( PS);D bconnection.closeconnection (conn);}} public static void Querypreparebyid (Integer id) {Connection conn = Dbconnection.getconnection (); PreparedStatement PS = null; ResultSet rs = null;try {PS = conn.preparestatement ("SELECT * from user where id =?"); Ps.setint (1, id);//Set placeholder parameter rs = Ps.executequery (); while (Rs.next ()) {User user = new User (); User.setid (Rs.getint ("id")); User.setusername (rs.getstring ("username")); User.setpassword (rs.getstring ("password")); User.setgender ( Rs.getboolean ("gender")); User.setregtime (Rs.getdate ("Regtime")); System.out.println (User.tostring ());}} catch (SQLException e) {e.printstacktrace ();} finally {Dbconnection.closeresultset (RS);D bconnection.closestatement ( PS);D bconnection.closeconnection (conn);}}}

7. Test Cases

Package Com.andieguo.jdbc;import Junit.framework.testcase;public Class Dbhelpertest extends TestCase {public void Getconnectiontest () {System.out.println (dbconnection.getconnection ());} public void Queryallbystatementtest () {dbhelper.queryallbystatement ();} public void Queryallbypreparestatementtest () {dbhelper.queryallbypreparestatement ();} public void Querybyidtest () {Dbhelper.querybyid (2);} public void Querybyprepareidtest () {Dbhelper.querypreparebyid (3);}}

8. Reference

JDBC Example tutorial–drivers, Connection, Statement and ResultSet (recommended)

Java code for connecting Mysql database and using Arraylist type

9, you may be interested in "Java programming" write, read, traverse properties file reprint Please specify the source: http://blog.csdn.net/andie_guo/article/details/25737031, thank you!