Android Study Notes (21) ---- connect to the server database using JDBC

/*************************************** **************************************** *************
* Author: conowen @ Dazhong
* E-mail: conowen@hotmail.com
* Http://blog.csdn.net/conowen
* Note: This article is original and only used for learning and communication. For more information, indicate the author and its source.

**************************************** **************************************** ************/

1. How to communicate with the server database on the Android platform

On the Android platform, you can connect to the database management system (DBMS) of the computer server, such as MySQL, PostgreSQL, Oracle, Sybase, and Microsoft sqlserver. The following two methods are provided:

Method 1. Direct Connection

Introduce the JDBC driver in the android project to connect directly. (This article mainly introduces this method)

 

Method 2: Indirect Connection

On the server, PHP + DBMS is used as the server side. php encapsulates DBMS data in JSON or XML format. Then the encapsulated data is returned to the Android platform.

Note:

The main problem with using the JDBC method is that the security is not high, and once the amount of data to be accessed is too large, it is prone to problems. In addition, the android system has APIs for direct JSON or XML parsing. Therefore, we recommend that you use the second method to improve the practicability and security.

2. Introduction to JDBC

JDBC is the abbreviation of Java Data Base connectivity. It refers to "Java Database Connection" and is composed of a group of classes and interfaces written in Java, provides standard APIs for Java-layer direct operations on relational databases. The principle is very simple, mainly because the Server DBMS sends SQL (Structured Query Language) commands first. Perform operations on various databases.

3. How to Use JDBC to communicate with the server database in Android

The main steps for connecting to a database using JDBC in the android project are as follows:

Load the JDBC driver -------> establish a connection ---------> send an SQL statement

3.1 load the JDBC driver

To use JDBC in the android project, import the JDBC driver.

Detailed process:

Select a project in eclipse, right-click ----> properties ----> select "Java build path" on the left ----> switch to "Libraries" ----> select "add external jars" ----> select the jar package of jtds ----> finished

Then, import the JDBC package at the beginning of the Java code. In this step, depending on the driver, the directory may be different. Find the driver path.

import net.sourceforge.jtds.jdbc.Driver;

Next, use the following statement in Java code to load the JDBC driver.

Class. forname ("net. SourceForge. jtds. JDBC. Driver"); // load the driver

Note: ADT version and android-sdk_Tools is best 16, I tried version 20 always appear unable to find the driver problem.

Downgrade method:

Install ADT offline

Download SDK tools

Add download task

Http://dl.google.com/android/installer_r16-windows.exe

After the download is complete, install the SDK to any location, and copy the files and folders to the previous SDK tools directory to overwrite the new version. Then open the android SDK manager from eclipse, and then check the android SDK platform-tools option in the tools section of the first item. Remember not to check the android SDK tool.

3.2 establish a connection

The JDBC drivers for each type of DBMS are different. The same DBMS also has several JDBC drivers, such as Microsoft SQL Server's JDBC drivers, the JDBC driver officially provided by Microsoft and the non-open source JDBC Driver (jtds) are recommended, with few bugs and completely open source code. Currently, jtds only supports Microsoft SQL Server and Sybase.

Since DBMS and JDBC drivers are different, the string Writing Method for each JDBC Connection database is also different.

Below are several common string writing formats for establishing a connection between JDBC and DBMS. (This blog post uses jtds to connect msserver, so it is 5th)

// 1. mySQL (http://www.mysql.com) mm. mysql-2.0.2-bin.jarConnection con = NULL; Class. forname ("org. gjt. mm. mySQL. driver "); // load the driver con = drivermanager. getconnection ("JDBC: mysql: // dbcomputernameoripaddr: 3306/databasename", username, password); // 2. postgreSQL (http://www.de.postgresql.org) pgjdbc2.jarconnection con = NULL; Class. forname ("org. postgreSQL. driver "); // load the driver con = drivermanager. getconnection ("JDBC: PostgreSQL: // dbcomputernameoripaddr/databasename", username, password); // 3. oracle (http://www.oracle.com/ip/deploy/database/oracle9i/ connector classes12.zip connection con = NULL; Class. forname ("oracle. JDBC. driver. oracledriver "); // load the driver con = drivermanager. getconnection ("JDBC: oracle: thin: @ dbcomputernameoripaddr: 1521: databasename", username, password); // 4. sybase (http://jtds.sourceforge.net) jconn2.jarconnection con = NULL; Class. forname ("com. sybase. jdbc2.jdbc. sybdriver "); // load the driver con = drivermanager. getconnection ("JDBC: Sybase: TDS: dbcomputernameoripaddr: 2638/databasename", username, password); // (default-username/password: "dba"/"SQL ") // 5. microsoft sqlserver (http://jtds.sourceforge.net) connection con = NULL; Class. forname ("net. sourceForge. jtds. JDBC. driver "); // load the driver con = drivermanager. getconnection ("JDBC: jtds: sqlserver: // dbcomputernameoripaddr: 1433/databasename", username, password); // 6. microsoft sqlserver (http://www.microsoft.com) connection con = NULL; Class. forname ("com. microsoft. JDBC. sqlserver. sqlserverdriver "); // load the driver con = drivermanager. getconnection ("JDBC: Microsoft: sqlserver: // dbcomputernameoripaddr: 1433; databasename = Master", username, password );

3.3 send SQL statements

After successfully connecting to the database, you can send statements for database operations and process the results.

Before sending an SQL statement, you must first create a statement object. The main task of statement is to send the SQL statement to the DBMS.

Statement stmt = con. createstatement (); // create a statement

Then, send the SQL statement. For select operations, the executequery (SQL) method of the statement object is used. For some operations on table creation and table modification, the executeupdate (SQL) method of the statement object is used.

For example:

String SQL = "select * From table_test"; // query all the contents of the table named "table_test" Statement stmt = con. createstatement (); // create statementresultset rs = stmt.exe cutequery (SQL );

4. Simple demo program

To connect to the Internet, you must add the permission to connect to the network in androidmanifest. xml:

<uses-permission android:name="android.permission.INTERNET" />

The complete androidmanifest. XML is as follows:

<?xml version="1.0" encoding="utf-8"?><manifest xmlns:android="http://schemas.android.com/apk/res/android"    package="com.conowen.sqlserver"    android:versionCode="1"    android:versionName="1.0" >    <uses-sdk android:minSdkVersion="9" />    <application        android:icon="@drawable/ic_launcher"        android:label="@string/app_name" >        <activity            android:label="@string/app_name"            android:name=".Android_connect_sqlserverActivity" >            <intent-filter >                <action android:name="android.intent.action.MAIN" />                <category android:name="android.intent.category.LAUNCHER" />            </intent-filter>        </activity>    </application><uses-permission android:name="android.permission.INTERNET" /></manifest>

Connect to Microsoft SQL Server, and then output the result in the background system. Out. println:

Result chart:

/* Author: conowen * Date: 2012.4.7 * android_connect_sqlserveractivity */package COM. conowen. sqlserver; import Java. SQL. connection; import Java. SQL. drivermanager; import Java. SQL. resultset; import Java. SQL. statement; import android. app. activity; import android. database. sqlexception; import android. OS. bundle; public class android_connect_sqlserveractivity extends activity {/** called when the activity is first created. * // @ overridepublic void oncreate (bundle savedinstancestate) {super. oncreate (savedinstancestate); setcontentview (R. layout. main); string username = "test"; // username string Password = "test"; // password connection con = NULL; try {// load the driver class. forname ("net. sourceForge. jtds. JDBC. driver "); con = drivermanager. getconnection ("JDBC: jtds: sqlserver: // 192.168.1.2: 1433/testdb", username, password);} catch (classnotfoundexception e) {system. out. println ("Driver loading error");} catch (sqlexception e) {system. out. println (E. getmessage ();} catch (exception e) {system. out. println (E. getmessage ();} Try {testconnection (CON); // test database connection} catch (Java. SQL. sqlexception e) {// todo auto-generated catch blocke. printstacktrace () ;}} public void testconnection (connection con) throws Java. SQL. sqlexception {try {string SQL = "select * From table_test"; // query all statement stmt = con. createstatement (); // create statementresultset rs = stmt.exe cutequery (SQL); // resultset is similar to cursorwhile (RS. next ()){//ResultSetInitially pointing to the first line of system. out. println (RS. getstring ("test_id"); // output the nth row. The column name is the value of "test_id" system. out. println (RS. getstring ("test_name");} Rs. close (); stmt. close ();} catch (sqlexception e) {system. out. println (E. getmessage (). tostring ();} finally {If (con! = NULL) Try {con. Close () ;}catch (sqlexception e ){}}}}