Ways to connect to a database

1, using the tool class and its configuration file method, connected to the background of the database

"Pros: You can connect to different databases and set the type of the database individually"

"Disadvantage: The database operation of the SQL statement, compared to a single, when the database table operations, SQL statements need to write in the corresponding method, when the error, change it is not easy to find, more trouble."

Configuration of related property objects of the Init.properties database

ip=127.0.0.1//IP Address port=1433   //Port number Dbtype=sqlserver   //Database type DBNAME=FDX  //database name Username=sa User name password=abc1234565//password

　　

Dbutils Tool class "Connecting to Database Toolkit"

Package Com.fdx.utils;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.util.Properties; public class Dbutils {private static Properties p = new Properties (); static{try {p.load ( DBUtils.class.getResourceAsStream ("init.properties"));} catch (IOException e) {//TODO auto-generated catch blockSystem.out.println ("configuration file not Found");}} Create connection database connection public static Connection getconnection () {Connection conn = null; String IP = p.getproperty ("IP"). Trim (). toLowerCase (); String port = p.getproperty ("port"). Trim (). toLowerCase (); String dbname = P.getproperty ("dbname"). Trim (). toLowerCase (); String DbType = P.getproperty ("DbType"). Trim (). toLowerCase (); String username = p.getproperty ("username"). Trim (). toLowerCase (); String Password = p.getproperty ("password"). Trim (). toLowerCase ();

/**
* Depending on the DBTYPE, choose a different database type
*
/
if (Dbtype.equals ("Oracle")) {//Connect the Oracle database try {class.forname ("Oracle.jdbc.driver.OracleDriver");} catch ( ClassNotFoundException e) {System.out.println ("Driver class not Found" +e.getmessage ());} Get Connection object StringBuffer url = new StringBuffer (); Url.append ("jdbc:oracle:thin:@"); Url.append (IP); url.append (":"); Url.append (port); Url.append (":"); Url.append (dbname); try {conn= drivermanager.getconnection (url.tostring (), Username,password);} catch (SQLException e) { SYSTEM.OUT.PRINTLN ("Database connection entry Error");} }if (dbtype.equals ("MySQL")) {//connection to MySQL database
The URL to the MySQL database looks like this:
Jdbc:mysql://localhost:3306/mysql?characterencoding=utf8

} if (Dbtype.equals ("SQL Server")) {
To connect to the SQL Server database, the URL path format is certain: Jdbc_url=jdbc:sqlserver://127.0.0.1:1433; DATABASENAME=FDX，
Where the port number must be followed by a semicolon, using the other is not feasible try {class.forname ("Com.microsoft.sqlserver.jdbc.SQLServerDriver");} catch ( ClassNotFoundException e) {System.out.println ("Driver class not Found" +e.getmessage ());} Get Connection object StringBuffer url = new StringBuffer (); Url.append ("jdbc:sqlserver://"); Url.append (IP); url.append (":"); Url.append (port); Url.append (";");
Url.append ("Databasename="); Url.append (dbname); try {conn= drivermanager.getconnection (url.tostring (), Username,password);} catch (SQLException e) { SYSTEM.OUT.PRINTLN ("Database connection entry Error");} }return Conn;} Close the Connection object in the database public static void Close (Connection conn,preparedstatement Pstat,resultset rs) {if (conn! = null) {try { Conn.close ();} catch (SQLException e) {//TODO auto-generated catch Blocke.printstacktrace ();}} if (pstat! = null) {try {pstat.close ()} catch (SQLException e) {//TODO auto-generated catch Blocke.printstacktrace ();}} if (rs! = null) {try {rs.close ()} catch (SQLException e) {//TODO auto-generated catch Blocke.printstacktrace ();}}}

　　

Method Two: The use of the Spring+ibatis framework, specifically a xxxsqlmap.xml configuration file, specifically put SQL statements of the file, design to the SQL statement method, direct call can be "own test of the DAO interface, Where the method name is consistent with the ID value of the SQL statement in Sqlmap, it is possible to establish a connection to the backend database;

--------{entity class, entity class Sqlmap configuration file, can be directly generated, the concrete directly generated in the way, to be continued,,,,,}

Property object configuration file for connection database config.properties

Driverclassname=com.microsoft.sqlserver.jdbc.sqlserverdrivervalidationquery=select 1jdbc_url=jdbc:sqlserver:// 127.0.0.1:1433;databasename=fdxjdbc_username=sajdbc_password=abc123456

Then in the Spring-mybatis.xml configuration file, configure the relevant information about the connected database "data source"

<bean id= "DataSource" class= "Com.alibaba.druid.pool.DruidDataSource" init-method= "Init" destroy-method= "Close" >  <property name= "Driverclassname" value= "${driverclassname}" ></property><property name= " URL "value=" ${jdbc_url} "/><property name=" username "value=" ${jdbc_username} "/><property name=" password "Value=" ${jdbc_password} "/>
......

Configure the relevant SQL statement in the appropriate SQLMAP configuration file

......

DAO interface, the method name is "Remember" to match the ID value of SQL in the Sqlmap configuration file

Like what:

Public interface Userdao {//query user information by ID (OK) public user selectbyprimarykey (String ID)//query All information (OK) public list<user > GetAll ();//query Maximum ID value (OK) public String selectmaxid ();//delete user Information "based on ID" (OK) public void Deletebyprimarykey (String id); /Insert user information (OK) public void Insert (user user);//Selective insert operation (OK) public void insertselective (user user);

Service business Logic Interface

Public interface UserService {public User Getuserbyid (string id);//(OK)//public void Deletebyid (string id);//(OK) public void Insertuser (user user),//(OK) public list<user> getalluser ();//(OK)//public void Update (user user);//(OK) Modify user password by ID, name, role, birthday public void insertselect (user user);//Selective insert operation (OK)

Serviceimpl Implementation Class

Package Fdx.service;import Java.util.date;import Java.util.list;import Org.springframework.beans.factory.annotation.autowired;import Org.springframework.stereotype.service;import Fdx.dao.userdao;import fdx.model.user;import Fdx.service.UserService; @Service ("UserService") public class Userserviceimpl implements UserService {private Userdao userdao;public Userdao Getuserdao () {return userdao;} @Autowiredpublic void Setuserdao (Userdao userdao) {This.userdao = Userdao;} Query user information by ID (OK) public user Getuserbyid (String ID) {return userdao.selectbyprimarykey (ID);} Delete user information by id public void Deletebyid (String id) {System.out.println ("Delete User ID" +id); System.out.println ("Name of user information:" +userdao.selectbyprimarykey (ID). GetName ()); Userdao.deletebyprimarykey (ID); System.out.println ("Deleted successfully!! ");} Insert user information, PRIMARY key auto Grow (OK) public void Insertuser (user user) {///call method, get to Maximum id value string id = Userdao.selectmaxid (); System.out.println ("Gets the maximum ID value:" +id); int num = Integer.parseint (Id.trim ());//when there is no trim () method, an exception occurs; Integer.parseint ( STR), the parameter can only be a purely numericCombination to convert, so use the trim () method to remove the space, and the resulting ID value has a space in it; num = num +1;//Converts a number to a string format, string sid = String.valueof (num);//to the primary key ID, Date of birth (gets the date of the current system) set; User.setid (SID); User.setbirth (new Date ()); Userdao.insert (user); System.out.println ("After insert: Test succeeded");} Query all user information (OK) public list<user> getalluser () {list<user> list = Null;list = Userdao.getall (); return List;}}

"Modular test Mode" for JUnit testing

Package Fdx.test;import Java.text.parseexception;import Java.text.simpledateformat;import java.util.Date;import Java.util.list;import Org.junit.test;import Org.junit.runner.request;import Org.springframework.context.applicationcontext;import Org.springframework.context.support.classpathxmlapplicationcontext;import Sun.applet.main;import Fdx.model.User; Import Fdx.service.userservice;public class Testmy {@Testpublic void Test () {ApplicationContext AC = new Classpathxmlapplicationcontext (new string[] {"Spring.xml", "Spring-mybatis.xml"}); UserService US = (userservice) ac.getbean ("UserService");//query User//system.out.println (US) by ID; User u = Us.getuserbyid ("1002"); SimpleDateFormat SF = new SimpleDateFormat ("Yyyy-mm-dd");　　　　System.out.println (U.getid () +u.getname () +u.getpwd () +sf.format (U.getbirth ())); }}

where the corresponding content in the Spring.xml configuration file:

<?xml version= "1.0" encoding= "UTF-8"? ><beans xmlns= "Http://www.springframework.org/schema/beans" xmlns: Xsi= "Http://www.w3.org/2001/XMLSchema-instance" xmlns:context= "Http://www.springframework.org/schema/context" Xsi:schemalocation= "http://www.springframework.org/schema/beanshttp://www.springframework.org/schema/beans/ Spring-beans-3.0.xsdhttp://www.springframework.org/schema/contexthttp://www.springframework.org/schema/context /spring-context-3.0.xsd "><!--the introduction of a property file for connecting to the database, classpath representing the--><context in the Src/main/resources directory: Property-placeholder location= "classpath:config.properties"/><!--Auto-scan (auto-inject), scan the corresponding service package-->< Context:component-scan base-package= "Fdx.service"/><context:annotation-config/></beans>

The resulting results are:

1002                          hahah                                   000000                                  2015-09-24

" access to the project, using the SSI framework, where the connection with the database, the operation of the background database, but also the use of the Sqlmap configuration file configuration SQL statements, but the difference is that they are encapsulated in the class to get the ID value of SQL Sqlmap, In order to get the corresponding SQL statement, we can get the desired SQL statement by invoking the method in the class directly. , and then realize the corresponding operation of the background database!!! "

Ways to connect to a database