how to connect to mysql with jdbc driver

By Maurizio Farina | Posted on Novemebr 2017

Java Database Connectivity (JDBC) is an API used to define how a Java application access to a Database. This post covers how to use JDBC to manage a MySQL database.

JDBC can be used to query statements (SELECT), to update stamentement (CREATE, INSERT, UPDATE and DELETE) or invoke stored procedures.

JDBC provides the following main classes:

  • Statement: used to send SQL statements
  • PreparedStatement: the SQL statements are cached and then executed
  • CallableStatement: used for executing stored procedures

How to connect to MySQL?

The connection method depends from the framework (such as Spring), application server (such S Wildfly), strategy (pooling for example) used by the application and from the JDBC technology to use.

JDBC technology drivers:

  • JDBC-ODBC bridge: is an implementation that uses ODBC driver to connect to the database. The driver converts JDBC method calls into ODBC function calls.
  • Native-API driver: is an implementation that uses the client-side libraries of the database. The driver converts JDBC method calls into native calls of the database API.
  • Network-Protocol driver (Middleware driver): also known as the Pure Java driver for database middleware is an implementation which makes use of a middle tier between the calling program and the database. The middle-tier (application server) converts JDBC calls directly or indirectly into a vendor-specific database protocol so the same client-side JDBC driver may be used for multiple databases depending on the number of databases the middleware has been configured to support.
  • Database-Protocol driver (Pure Java driver) or thin driver: is an implementation that converts JDBC calls directly into a vendor-specific database protocol (generally vendor supplied for example the widely used Oracle thin driver)

Here some example on how to retrieve a connection using Native-API from a MySQL server.

Directly from driver:

1
2
3
Class.forName("com.mysql.cj.jdbc.Driver");
Connection connection = null;
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/listfeeds", "feeds", "pwdfeeds");

Using Spring application context to define a new datasource

1
2
3
4
5
6
7
8
<bean id="listFeedsDS" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost:3306/listfeeds" />
    <property name="username" value="feeds" />
    <property name="password" value="feeds" />
    <property name="initialSize" value="5" />
    <property name="maxIdle" value="5" />
</bean>

The initialSize allows to specify the size of connection pool.

The class org.apache.commons.dbcp.BasicDataSource includes other useful parameters to manage the connection pool:

Parameter Description
initialSize initial number of connections that are created when the pool is started
maxTotal The maximum number of active connections that can be allocated from this pool at the same time, or negative for no limit.
maxIdle The maximum number of connections that can remain idle in the pool, without extra ones being released, or negative for no limit.
minIdle The minimum number of connections that can remain idle in the pool, without extra ones being created, or zero to create none.
maxWaitMillis The maximum number of milliseconds that the pool will wait (when there are no available connections) for a connection to be returned before throwing an exception, or -1 to wait indefinitely.

For all parameters take a look to BasicDataSource Configuration Parameters

Using jndi lookup to use the datasource defined in application server configuration file:

1
<jee:jndi-lookup id="listFeedsDS" jndi-name="java:jboss/datasources/LISTFEEDS" expected-type="javax.sql.DataSource" />

Defining a new datasource in Wildlfy server configuration file standalone.xml

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<datasource jta="false" jndi-name="java:jboss/datasources/LISTFEEDS" pool-name="LISTFEEDS" enabled="true" use-java-context="true" use-ccm="true">
    <timeout>
        <idle-timeout-minutes>9</idle-timeout-minutes>
    </timeout>
    <validation>
        <validate-on-match>true</validate-on-match>
        <use-fast-fail>true</use-fast-fail>                  
        <check-valid-connection-sql>select 1</check-valid-connection-sql>
        <background-validation>false</background-validation>
        <background-validation-millis>10000</background-validation-millis>
    </validation>
    <connection-url>jdbc:mysql://localhost:3306/listfeeds?autoReconnect=true&amp;useSSL=false</connection-url>
    <driver>mysql</driver>
    <pool>
        <min-pool-size>1</min-pool-size>
        <max-pool-size>15</max-pool-size>
        <prefill>false</prefill>
    </pool>
    <security>
        <user-name>listfeeds</user-name>
        <password>listfeeds</password>
    </security>
</datasource>

Using PrepareStatement

The following example is an extracted from ListFeeds project:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
String query = "insert into feeds (icon, type, source, title, latLong, description, address , url ,Date, city, guid) values (?, ?, ?, ?, point(?,?),?, ?, ?, ?, ?,?)";

try {

    PreparedStatement preparedStmt = connection.prepareStatement(query);

    preparedStmt.setInt(1, Integer.parseInt(nextLine[0])); 
    preparedStmt.setString(2, nextLine[1]); 
    preparedStmt.setString(3, nextLine[2]); 
    preparedStmt.setString(4, nextLine[3]); 

    String[] latLong = nextLine[4].split(",", -1);
    preparedStmt.setDouble(5, Double.parseDouble(latLong[0]));
    preparedStmt.setDouble(6, Double.parseDouble(latLong[1]));

    preparedStmt.setString(7, nextLine[5]); 
    preparedStmt.setString(8, nextLine[6]); 
    preparedStmt.setString(9, nextLine[7]); 
    preparedStmt.setDate(10, new java.sql.Date(df.parse(nextLine[8]).getTime()));

    preparedStmt.setString(11, nextLine[9]);

    preparedStmt.setString(12, nextLine[10]);

    preparedStmt.execute();

} catch (SQLException e) {
    log.error("SQL Exception", e);

} catch (ParseException e) {

    log.error("ParseException", e);
}

Calling a Stored Procedure

Is possible to call a stored procedure using CallableStatement. This statement returns a ResultSet.

1
2
3
4
5
CallableStatement cstmt = con.prepareCall({CALL STORED_PROCEDURE (?)}); 
cstmt.setString(1, feedId); 
ResultSet rs = cstmt.executeQuery(); 
rs.next(); 
String feedTitle = rs.getString(1); 

Reading a table

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(
        "SELECT icon, type, source, title, asText(latLong), description, address , url ,date, city, guid FROM listfeeds.feeds where MBRWithin(latLong,ST_GeomFromText('POLYGON((44.54791 11.230774, 44.54791 11.435394, 44.428386 11.435394, 44.428386 11.230774, 44.54791 11.230774))'))");

while (rs.next()) {

    StringBuffer s = new StringBuffer();

    s.append("icon[" + rs.getInt(1) + "] "); 
    s.append("type[" + rs.getString(2) + "] ");
    s.append("source[" + rs.getString(3) + "] ");
    s.append("title[" + rs.getString(4) + "] "); 
    s.append("latLong[" + rs.getString(5) + "] ");
    //s.append("description[" + rs.getString(6) + "] "); // description
    s.append("address[" + rs.getString(7) + "] "); 
    s.append("url[" + rs.getString(8) + "] "); 
    s.append("date[" + df.format(rs.getDate(9) )+ "] ");
    s.append("city[" + rs.getString(10) + "] "); 
    s.append("guid[" + rs.getString(11) + "] "); 

    log.info(s.toString());
}
conn.close();

JDBC Data Types

JDBC Types Java Type
CHAR, VARCHAR,LONGVARCHAR java.lang.String
CLOB java.sql.Clob
BLOB java.sql.Blob
NUMERIC, DECIMAL java.math.BigDecimal
BIT, BOOLEAN Boolean
BINARY, VARBINARY,LONGVARBINARY byte[]
DATE java.sql.Date
TIME java.sql.Time
TIMESTAMP java.sql.Timestamp
TINYINT byte
SMALLINT short
INTEGER int
BIGINT long
REAL float
FLOAT, DOUBLE double

Improving

Topics to introduce in this post:

  • Pooling
  • Performance
  • Metadata
  • Network Traffic Reduction
  • Debugging and Logging
  • Encrypt Your Data Using SSL
  • Bulk Loading
  • Few notes about Hibernate