Welcome to JDBC course Notes

written by: Sadi Evren SEKER

JDBC is used for connecting java codes to a database. A Database, java code and a bridge are essentials for jdbc. During this course we will use mysql database and a native mysql java bridge.
Although you can connect to the database by using an indirect connection method called JDBC:ODBC bridge which can be found in the J2SE packet, we will install an external bridge. First of all you need to set your environment up.
1. Install a database, and be sure that you can connect your database with your user name and password.
in our case you will use the mysql databases, you can connect those databases by using the "mysql" command in the shell. After starting the mysql interface you need to connect your database server by typing "connect mydb".
2. Install J2SE package. this installation is done in /usr/java/j2se1.4.0 directory. You can use java, javac or any binary from /bin under this directory.
3. Install the connection bridge. Since we will use the mysql-java connection we can use the native driver for mysql.
this native driver is installed in your home directory. (in our case mysql-connector-java-2.0.14/ directory contains a mysql-connector-java-2.0.14-bin.jar file.) this file will create the connection between java and mysql. The only thing you need to do is set your classpath environment variable to this file by the following command:
export CLASSPATH=.:~/mysql-connector-java-2.0.14/mysql-connector-java-2.0.14-bin.jar
the "." stands for current directory and you need to include this in your classpath, else your current project will be hidden from java. Please notice that you can seperate the directories by ":" symbol.

Coding:


//sample database connection file from java via jdbc to mysql
//Do not forgetto include mysql connection jar file in your classpath
//written by Sadi Evren SEKER 02/04/2003 18:33


import java.sql.*;


public class sadijdbc
{

public static void main(String args[])
 {

        try {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        System.out.println("Connecting = jdbc:mysql://10.2.13.201/mysql?user=root");

        Connection conn = DriverManager.getConnection("jdbc:mysql://10.2.13.201/g2?user=root");

//        conn.createStatement().executeUpdate();


//CREATING TABLE
       String sql = "create table xyz(x int, y varchar(30),z date)"; 
       PreparedStatement pst = conn.prepareStatement(sql);
        pst.executeQuery();
        pst.close();
//INSERTING INTO TABLE
      sql = "insert into xyz values(3656, 'karakter mi karakter', '2003-02-01')"; 
       pst = conn.prepareStatement(sql);
        pst.executeQuery();
        pst.close();
      sql = "insert into xyz values(3657, 'yazi mi yazi', '2003-02-02')"; 
       pst = conn.prepareStatement(sql);
        pst.executeQuery();
        pst.close();
//SELECTING FROM TABLE
        sql="select * from xyz";
        pst=conn.prepareStatement(sql);
        ResultSet rs=pst.executeQuery();
        while(rs.next()){
                int i=rs.getInt(1);
                String s=rs.getString(2);
                System.out.println("i:"+i+"s:"+s);
        }
        pst.close();
//DROPPING TABLE
       sql = "drop table xyz"; 
       pst = conn.prepareStatement(sql);
        pst.executeQuery();
        pst.close();
//CLOSING CONNECTION
        conn.close();
        }catch(Exception e) {e.printStackTrace();}
 }
}

you need to import the java.sql.* library for the sql classes. Here we are writing a java application. We need exception handling by default and we will catch the SQLException.
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conn = DriverManager.getConnection("jdbc:mysql://10.2.13.201/g2?user=root");
our connection bridge is the com.mysql.jdbc.Driver (which is already declared in your classpath environment variable).
and we set the bridge to our database at 10.2.13.201 server with the sid g2 and username root, there is no passwd declaration for this connection.
prepareStatement() function passes the given sql query in string to the connection bridge and executeQuery() function executes this query. DDL queries like insert, delete, update or create do not need any result sets. But select queries need a result set.
Since select queries turns back a result, we need to catch this result in a ResultSet, we can traverse the result set by next() function.
while traversing the result set, we need to parse the result data by using getXXX functions. the following table keeps the types:

 

T
I
N
Y
I
N
T

S
M
A
L
L
I
N
T

I
N
T
E
G
E
R

B
I
G
I
N
T

R
E
A
L

F
L
O
A
T

D
O
U
B
L
E

D
E
C
I
M
A
L

N
U
M
E
R
I
C

B
I
T

C
H
A
R

V
A
R
C
H
A
R

L
O
N
G
V
A
R
C
H
A
R

B
I
N
A
R
Y

V
A
R
B
I
N
A
R
Y

L
O
N
G
V
A
R
B
I
N
A
R
Y

D
A
T
E

T
I
M
E

T
I
M
E
S
T
A
M
P

getByte

X

x

x

x

x

x

x

x

x

x

x

x

x

 

 

 

 

 

 

getShort

x

X

x

x

x

x

x

x

x

x

x

x

x

 

 

 

 

 

 

getInt

x

x

X

x

x

x

x

x

x

x

x

x

x

 

 

 

 

 

 

getLong

x

x

x

X

x

x

x

x

x

x

x

x

x

 

 

 

 

 

 

getFloat

x

x

x

x

X

x

x

x

x

x

x

x

x

 

 

 

 

 

 

getDouble

x

x

x

x

x

X

X

x

x

x

x

x

x

 

 

 

 

 

 

getBigDecimal

x

x

x

x

x

x

x

X

X

x

x

x

x

 

 

 

 

 

 

getBoolean

x

x

x

x

x

x

x

x

x

X

x

x

x

 

 

 

 

 

 

getString

x

x

x

x

x

x

x

x

x

x

X

X

x

x

x

x

x

x

x

getBytes

 

 

 

 

 

 

 

 

 

 

 

 

 

X

X

x

 

 

 

getDate

 

 

 

 

 

 

 

 

 

 

x

x

x

 

 

 

X

 

x

getTime

 

 

 

 

 

 

 

 

 

 

x

x

x

 

 

 

 

X

x

getTimestamp

 

 

 

 

 

 

 

 

 

 

x

x

x

 

 

 

x

x

X

getAsciiStream

 

 

 

 

 

 

 

 

 

 

x

x

X

x

x

x

 

 

 

getUnicodeStream

 

 

 

 

 

 

 

 

 

 

x

x

X

x

x

x

 

 

 

getBinaryStream

 

 

 

 

 

 

 

 

 

 

 

 

 

x

x

X

 

 

 

getObject

x

x

x

x

x

x

x

x

x

x

x

x

x

x

x

x

x

x

x

you can use the getxxx functions by both couloumn number and coloumn name. For example in our case we have a database named xyz and it has 3 coloumns, integer x, varchar y and the date z. As you can mention in the source code, we have the getInt(1) and getString(2) functions. You would use the getInt("x") and getString("y") functions as well.
Last updated: 06/04/2003 15:43, by Sadi Evren SEKER