Database connection, the implementation of adding or removing changes to the specific steps (full)

1. Necessary steps to connect to the database:

Jdbc:

Driver=com.mysql.jdbc.driver
Jdbcurl=jdbc:mysql://localhost:3306/lxn
User=root
Password=lxn123

Oracle

#driver =oracle.jdbc.driver.oricerdriver
#jdbcUrl =jdbc:oracle:thin: @localhost: 1521:ORCL
#user =system
#password =lxn123

2. Two ways to connect the database, to realize the database additions and deletions

Package com.lanqiao.javatest;

Import java.io.IOException;
Import Java.io.InputStream;
Import java.sql.Connection;
Import Java.sql.Date;
Import Java.sql.Driver;
Import Java.sql.DriverManager;
Import Java.sql.ResultSet;
Import java.util.Properties;

Import Org.junit.Test;

Import com.mysql.jdbc.Statement;

public class Testdrivermanager {
/*
* How to connect a database:
* 1. Connect to the database using DriverManager
* 2.Driver Connection: Is an interface, is a local implementation of the interface, from which to obtain the database of the driver link
* */

Method One: DriverManager method, connect to database

public void Testdrivermanager () throws exception{
Connect four elements of a database:
String Driverclass=null;
String Jdbcurl=null;
String User=null;
String Password=null;

Read the jdbc.properties under the Classpath file
InputStream in=
GetClass (). getClassLoader (). getResourceAsStream ("jdbc.properties");
Properties Properties=new properties ();
Properties.load (in);//read file from input stream

Get the files in properties in turn
Driverclass=properties.getproperty ("Driver");
Jdbcurl=properties.getproperty ("Jdbcurl");
User=properties.getproperty ("user");
Password=properties.getproperty ("password");

Class.forName (Driverclass);//reflection mechanism gets the driverclass in the file

Get the data connection through the Getconnection method in DriverManager
Connection conn=drivermanager.getconnection (Jdbcurl,user,password);
SYSTEM.OUT.PRINTLN (conn);
}

Method Two: Connect to the database using the driver interface
1.
Public Connection getconnection () throws exception{
The connection to the database user, there is a connection interface, to achieve data connection, and can be deleted from the database to change the operation
Four necessary steps in the connection data:
String Driverclass=null;
String Jdbcurl=null;
String User=null;
String Password=null;

Read the Jdbc.properties file under the Classpath file
InputStream In=getclass (). getClassLoader (). getResourceAsStream ("jdbc.properties");
Properties Properties=new properties ();
Reading files from the input stream
Properties.load (in);

Get the contents of the file in turn
Driverclass=properties.getproperty ("Driver");
Jdbcurl=properties.getproperty ("Jdbcurl");
User=properties.getproperty ("user");
Password=properties.getproperty ("password");

Reflection Mechanism Acquisition
@SuppressWarnings ("unused")
Driver driver= (Driver) class.forname (Driverclass). newinstance ();//Gets the methods and properties of the runtime class

Properties Info=new properties ();
Info.put ("User", "root");
Info.put ("Password", "lxn123");
Connection Conn=driver.connect (Jdbcurl, info);
Return conn;
}

Test Testdriver () method;
public void TestDriver1 () throws exception{
System.out.println (getconnection ());
}
/*
* 1. Add, delete, change, and query operations to the specified database after connecting to the database
* Statement the object used to execute the SQL statement;
* Using Connection's Statestatement () method to obtain
* Same over executeupdate (SQL) can execute SQL statement
* Incoming SQL statements can be insect (insert), update (modify), delete (delete), but not select (query)
* 2. It is important to note that connection,statement are all linked resources of the application and database server and are closed after use;
* */

public void Teststatement () throws exception{//database Insert, delete, change, query required steps;
1. Get the database
Connection connn=getconnection ();
2. Preparing the inserted SQL statement

Inserting an SQL statement into a table created in the database
4. Actions for inserting, deleting, changing, querying

Insert
String sql1= "INSERT into CUSTOMER (Name,emall,birth) VALUES (' Pa ', ' LJ ', ' 1932-2-12 ');";
Delete
String sql2= "Delete from customer where id=27";
Modify
String sql3= "Update customer set Name= ' Jiajia ' where id=2564";
Gets the statement object of the SQL statement, calls the statement object's executeupdate (SQL), executes the insert of the SQL statement
Statement statement= (Statement) connn.createstatement ();
Statement.executeupdate (SQL1);
Statement.executeupdate (SQL2);
Statement.executeupdate (SQL3);
5 Close the Statement object.
Statement.close ();
3. Close the connection
Connn.close ();
}
SQL is a database statement, this is a method for adding and deleting databases
public void TestStatement1 (String sql) {
Connection Conn=null;
Statement Sta=null;
try {
Conn=getconnection ();
Sta= (Statement) conn.createstatement ();
Sta.executeupdate (SQL);
} catch (Exception e) {
E.printstacktrace ();
}finally{
if (conn!=null) {
try {
Conn.close ();
} catch (Exception e) {
E.printstacktrace ();
}
}
if (sta!=null) {
try {
Sta.close ();
} catch (Exception e) {
E.printstacktrace ();
}
}
}
}
Query methods for databases
@Test
public void Testresultset () {
Gets the property record value for the specified ID, or name, and prints
Connection Connection=null;
Statement Statement=null;
ResultSet Resultset=null;

try {
1. Get connection
Connection=getconnection ();

2. Get statement
statement= (statement) connection.createstatement ();

3. Preparing SQL statements
String sql= "Select Id,name,email,birth from Customer";

4. Execute the query and get resultset
Resultset=statement.executequery (SQL);

5. Disposal of ResultSet
while (Resultset.next ()) {
int Id=resultset.getint (1);
String name=resultset.getstring (2);
String email=resultset.getstring (3);
Date birth=resultset.getdate (4);
System.out.println (id+ "--" +name+ "--" +email+ "--" +birth ");
}

} catch (Exception e) {
Todo:handle exception
}
6. Closing Database Resources
finally{
Release (Connection,statement,resultset);
}
}

Ways to turn off database resources
public static void Release (Connection conn,statement Sta,resultset rs) {
if (conn!=null) {
try {
Conn.close ();
} catch (Exception e) {
E.printstacktrace ();
}
}
if (sta!=null) {
try {
Sta.close ();
} catch (Exception e) {
E.printstacktrace ();
}
}
if (rs!=null) {
try {
Rs.close ();
} catch (Exception e) {
E.printstacktrace ();
}
}
}
}

Database connection, the implementation of adding or removing changes to the specific steps (full)