- ? Create a program that connects to the database in JDBC with 7 steps:
- 1. Load the JDBC driver:
- Before connecting to the database, first load the driver of the database you want to connect to the JVM (Java Virtual machine),
- This is achieved through the static method forname (String className) of the Java.lang.Class class.
- For example:
static{
try {
Publish using
Context context = new InitialContext (); Create an up and down file object
DataSource = (DataSource) context.lookup ("Java:comp/env/jdbc/jspjndi");
Test using in development
Properties Props = new properties ();
Props.load (DbHelper.class.getClassLoader (). getResourceAsStream ("db.properties"));
DataSource = Basicdatasourcefactory.createdatasource (props);
} catch (Exception e) {
E.printstacktrace ();
}
}
- After a successful load, an instance of the driver class is registered in the DriverManager class.
- 2. Provide the URL of the JDBC connection
- ? The connection URL defines the protocol, sub-protocol, and data source identity when the database is connected.
- Writing form: Protocol: Sub-Protocol: Data source identification
- Protocol: Always start with JDBC in JDBC
- Sub-Protocol: A bridge-connected driver or database management system name.
- Data source identification: The tag locates the address of the database source and the connection port.
- For example: (MySQL connection URL)
- Jdbc:mysql:
- LOCALHOST:3306/TEST?USEUNICODE=TRUE&CHARACTERENCODING=GBK;
- Useunicode=true: Indicates the use of the Unicode character set. If Characterencoding is set to
- gb2312 or GBK, this parameter must be set to true . CHARACTERENCODING=GBK: The character encoding method.
- 3. Create a connection to the database
- To connect to a database, you need to request and obtain a connection object from Java.sql.DriverManager.
- The object represents a connection to a database.
Get database Connection object
public static Connection Getconn () {
Connection con = null;
if (DataSource! = null) {
try {
con = datasource.getconnection (); Fetch a connection in the data source
} catch (SQLException e) {
E.printstacktrace ();
}
}
return con;
}
- 4 Closing the Connection object
Public static void CloseAll (ResultSet rs,statement stmt,connection conn) {
//Close result set object
if (null!=rs) {
try {
rs.close ();
} catch (SQLException e) {
e.printstacktrace ();
}
}
//Close Statement Object
if (null!=stmt) {
try {
stmt.close ();
} catch (SQLException e) {
//TODO auto-generated catch block
e.printstacktrace ();
}
}
//Close Database Connection object
if (null!=conn) {
try {
conn.close ();
} catch (SQLException e) {
//TODO auto-generated catch block
e.printstacktrace ();
}
}
}
Public static void CloseAll (ResultSet rs,preparedstatement pstmt,connection conn) {
//Close result set object
if (null!=rs) {
try {
rs.close ();
} catch (SQLException e) {
e.printstacktrace ();
}
}
//Close Statement Object
if (null!=pstmt) {
try {
pstmt.close ();
} catch (SQLException e) {
//TODO auto-generated catch block
e.printstacktrace ();
}
}
//Close Database Connection object
if (null!=conn) {
try {
conn.close ();
} catch (SQLException e) {
//TODO auto-generated catch block
e.printstacktrace ();
}
}
}
- 5, the method of setting parameters
/**
* @param pstmt Precompiled objects
* @param the set of values passed by the params
* @throws SQLException
*/
private static void SetParams (PreparedStatement pstmt,object...params) throws sqlexception{
if (null!=params&¶ms.length>0) {
for (int i=0;i<params.length;i++) {
if (Params[i] instanceof Date) {
The params stores the values in the same order as the?
Pstmt.settimestamp (i+1, New Timestamp ((Date) params[i]). GetTime ());
}else{
Pstmt.setobject (i+1, params[i])//params store values in the same order as?
}
}
}
}
- 6. Increase and revise operation
/**
* Adding and deleting changes
* @param SQL
* @param params
* @return
* @throws SQLException
*/
public static int doupdate (String sql,object...params) {
Connection Conn =null;
preparedstatement pstmt =null;
int result =-1;
try {
conn=getconn ();
pstmt=conn.preparestatement (SQL);
SetParams (pstmt, params);
result =pstmt.executeupdate ();
} catch (SQLException e) {
//TODO auto-generated catch block
e.printstacktrace ();
}finally {
CloseAll (NULL, PSTMT, conn);
}
return result;
}
public static int doupdate (list<string> sqls, object[]...params) throws sqlexception{
Connection Conn =null;
preparedstatement pstmt =null;
int result =-1;
try {
conn = Getconn ();
Conn.setautocommit (false);
if (null!= sqls && sqls.size () >0) {
//Circular SQL statements
for (int i=0;i<sqls.size (); i++) {
String Sql=sqls.get (i);
pstmt =conn.preparestatement (SQL);
//set for current SQL statement
SetParams (pstmt, params[i]);
result +=pstmt.executeupdate ();
}
}
//Submit things
conn.commit ();
} catch (Exception e) {
e.printstacktrace ();
//Roll back things
result=0;
Conn.rollback ();
} finally {
//Reply to the scene
Conn.setautocommit (true);
CloseAll (NULL, PSTMT, conn);
}
return result;
}
public static int updateimg (String sql,int ID, file file) throws filenotfoundexception{
FileInputStream in = new FileInputStream (file);
Connection Conn =null;
preparedstatement pstmt =null;
int result = 0;
try {
pstmt = conn.preparestatement (sql);
Pstmt.setbinarystream (1, in, (int) file.length ());
Pstmt.setint (2, id);
result = Pstmt.executeupdate ();
} catch (SQLException e) {
} finally {
CloseAll (NULL, PSTMT, conn);
}
return result;
}
6. Query operation
/**
* Query multiple records
* @param SQL
* @param params
* @return
* @throws SQLException
*/
Public static list<map<string, object>> Findmultiobject (String sql,object...params) throws sqlexception{
list<map<string, object>> List =new arraylist<map<string,object>> ();
Connection Conn =null;
preparedstatement pstmt =null;
ResultSet rs =null;
map<string, object> Map =null;
try {
Conn =getconn ();
pstmt =conn.preparestatement (SQL);
SetParams (pstmt, params);
rs =pstmt.executequery ();
list<string > ColumnNames =getallcolumnnames (RS);//Get all the lists in the results
While (Rs.next ()) {
map = new hashmap<string,object> ();
For (String cn:columnnames) {//Loop column name, the list action Map key, based on the list gets the value of each column
Map.put (CN, Rs.getobject (CN));
}
list.add (map);
}
} finally {
CloseAll (RS, PSTMT, conn);
}
return list;
}
/**
* Query Single record select * from table name where id = 1
* @param SQL
* @param params
* @return
* @throws SQLException
*/
Public static map<string,object> findsingleobject (String sql, object...params) throws sqlexception{
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
map<string,object> Map = null;
try {
conn = Getconn ();
pstmt = conn.preparestatement (sql);
SetParams (pstmt, params);
rs = Pstmt.executequery ();
list<string> ColumnNames = Getallcolumnnames (RS);//Get all lists in the result set
While (Rs.next ()) {
map = new hashmap<string,object> ();
For (String cn:columnnames) {//Loop column name, the list action Map key, based on the list gets the value of each column
Map.put (CN, Rs.getobject (CN));
}
}
}finally{
CloseAll (RS, PSTMT, conn);
}
return map;
}
8. Operation result Operation
/**
* Get all column names based on the result set object there is a list of jdbc2.0 metadata in the collection
* @param RS
* @return
* @throws SQLException
*/
private static list<string> Getallcolumnnames (ResultSet rs) throws sqlexception{
list<string> columnnames =new arraylist<string> ();
if (null!=rs) {
for (int i=0; I<rs.getmetadata (). getColumnCount (); i++) {
Columnnames.add (Rs.getmetadata (). getColumnName (i+1));
}
}
return columnnames;
}
Public static<t> T Get (class<t> clazz, String sql, object...params) throws SQLException {
Return Mapping2obj (Clazz, Findsingleobject (SQL, params));
}
Public static <T> list<t> List (class<t> clazz, String sql, object...params) throws SQLException {
list<map<string, object>> results = findmultiobject (sql, params);
list<t> ts = null;
if (results! = null) {
ts = new arraylist<t> ();
for (map<string, object> result:results) {
Ts.add (Mapping2obj (clazz, result));
}
}
return TS;
}
private static <T> T mapping2obj (class<t> clazz, map<string,object> temps) {
T t = null;
try {
t = clazz.newinstance ();
} catch (Instantiationexception | Illegalaccessexception e) {
e.printstacktrace ();
}
method[] ms = Clazz.getdeclaredmethods ();//Take All methods
For (Method m:ms) {
String mn = M.getname ();//Fetch to Method name
if (Mn.startswith ("set")) {
String pt = m.getparametertypes () [0].getname ();///parameter type taken to the Set method
Object obj = Temps.get (Mn.replace ("Set", ""). toUpperCase ()); Takes the value of the set method corresponding to the database field
try {
if (obj! = null) {
if (pt.endswith ("int") | | | pt.endswith ("Integer")) {
M.invoke (t, ((BigDecimal) obj). Intvalue ());
} else if (Pt.endswith ("double") | | | pt.endswith ("double")) {
M.invoke (t, ((BigDecimal) obj). Doublevalue ());
} else if (Pt.endswith ("Java.util.Date")) {
M.invoke (t, (Timestamp) obj);//11g
//m.invoke (T, New Date ((java.sql.Date) obj). GetTime ());//10g
} else {
if (Obj.getclass (). GetName (). EndsWith ("CLOB")) {
obj = clobtostring ((CLOB) obj);
}
M.invoke (t, obj);
}
}
} catch (Exception e) {
e.printstacktrace ();
}
}
}
return t;
}
Public static String clobtostring (Clob Clob) {
String restring = "";
Reader is = null;
try {
is = Clob.getcharacterstream ();
} catch (SQLException e) {
e.printstacktrace ();
}
//Get stream
bufferedreader br = new BufferedReader (is);
String s = null;
try {
s = br.readline ();
} catch (IOException e) {
e.printstacktrace ();
}
stringbuffer sb = new StringBuffer ();
While (s! = null) {
//Execution loop takes all the strings out and pays the value to StringBuffer from StringBuffer to string
Sb.append (s);
try {
s = br.readline ();
} catch (IOException e) {
e.printstacktrace ();
}
}
restring = sb.tostring ();
return restring;
}
}