JDBC

Showing posts with label JDBC. Show all posts

Here's a step by step explanation how to install MySQL and JDBC and how to use it

<div dir="ltr" style="text-align: left;" trbidi="on">
<ol style="background-color: white; border: 0px; color: #222222; font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif; font-size: 15px; line-height: 19.5px; margin: 0px 0px 1em 30px; padding: 0px;">
<li style="border: 0px; margin: 0px 0px 0.5em; padding: 0px; word-wrap: break-word;"><div style="border: 0px; clear: both; margin-bottom: 1em; padding: 0px;">
<a href="http://dev.mysql.com/downloads/mysql/" style="border: 0px; color: #0c65a5; cursor: pointer; margin: 0px; padding: 0px; text-decoration: none;">Download</a> and install the MySQL server. Just do it the usual way. Remember the port number whenever you've changed it. It's by default <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">3306</code>.</div>
</li>
<li style="border: 0px; margin: 0px 0px 0.5em; padding: 0px; word-wrap: break-word;"><div style="border: 0px; clear: both; margin-bottom: 1em; padding: 0px;">
<a href="http://dev.mysql.com/downloads/connector/j/" style="border: 0px; color: #0c65a5; cursor: pointer; margin: 0px; padding: 0px; text-decoration: none;">Download</a> the JDBC driver and put in classpath, extract the ZIP file and put the containing JAR file in the classpath. The vendor-specific JDBC driver is a concrete implementation of the<a href="http://docs.oracle.com/javase/8/docs/api/java/sql/package-summary.html" style="border: 0px; color: #0c65a5; cursor: pointer; margin: 0px; padding: 0px; text-decoration: none;">JDBC API</a> (<a href="http://docs.oracle.com/javase/tutorial/jdbc/index.html" style="border: 0px; color: #0c65a5; cursor: pointer; margin: 0px; padding: 0px; text-decoration: none;">tutorial here</a>).</div>
<div style="border: 0px; clear: both; margin-bottom: 1em; padding: 0px;">
If you're using an IDE like Eclipse or Netbeans, then you can add it to the classpath by adding the JAR file as Library to the Build Path in project's properties.</div>
<div style="border: 0px; clear: both; margin-bottom: 1em; padding: 0px;">
If you're doing it "plain vanilla" in the command console, then you need to specify the path to the JAR file in the <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">-cp</code> or <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">-classpath</code> argument when executing your Java application.</div>
<pre style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin-bottom: 1em; max-height: 600px; overflow: auto; padding: 5px; width: auto; word-wrap: normal;">java -cp .;/path/to/mysql-connector.jar com.example.YourClass</pre>
<div style="border: 0px; clear: both; margin-bottom: 1em; padding: 0px;">
The <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">.</code> is just there to add the current directory to the classpath as well so that it can locate <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">com.example.YourClass</code> and the <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">;</code> is the classpath separator as it is in Windows. In Unix and clones <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">:</code> should be used.</div>
</li>
<li style="border: 0px; margin: 0px 0px 0.5em; padding: 0px; word-wrap: break-word;"><div style="border: 0px; clear: both; margin-bottom: 1em; padding: 0px;">
Create a <a href="http://dev.mysql.com/doc/refman/5.7/en/create-database.html" style="border: 0px; color: #0c65a5; cursor: pointer; margin: 0px; padding: 0px; text-decoration: none;">database</a> in MySQL. Let's create a database <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">javabase</code>. You of course want World Domination, so let's use UTF-8 as well.</div>
<pre class="default prettyprint prettyprinted" style="background-color: #eeeeee; border: 0px; color: #393318; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin-bottom: 1em; max-height: 600px; overflow: auto; padding: 5px; width: auto; word-wrap: normal;"><code style="border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; margin: 0px; padding: 0px; white-space: inherit;">CREATE DATABASE javabase DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;</code></pre>
</li>
<li style="border: 0px; margin: 0px 0px 0.5em; padding: 0px; word-wrap: break-word;"><div style="border: 0px; clear: both; margin-bottom: 1em; padding: 0px;">
<a href="http://dev.mysql.com/doc/refman/5.7/en/adding-users.html" style="border: 0px; color: #0c65a5; cursor: pointer; margin: 0px; padding: 0px; text-decoration: none;">Create an user</a> for Java and <a href="http://dev.mysql.com/doc/refman/5.7/en/grant.html" style="border: 0px; color: #0c65a5; cursor: pointer; margin: 0px; padding: 0px; text-decoration: none;">grant</a> it access. Simply because using <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">root</code> is a bad practice.</div>
<pre class="default prettyprint prettyprinted" style="background-color: #eeeeee; border: 0px; color: #393318; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin-bottom: 1em; max-height: 600px; overflow: auto; padding: 5px; width: auto; word-wrap: normal;"><code style="border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; margin: 0px; padding: 0px; white-space: inherit;">CREATE USER 'java'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON javabase.* TO 'java'@'localhost' IDENTIFIED BY 'password';</code></pre>
<div style="border: 0px; clear: both; margin-bottom: 1em; padding: 0px;">
Yes, <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">java</code> is the username and <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">password</code> is the password here.</div>
</li>
<li style="border: 0px; margin: 0px 0px 0.5em; padding: 0px; word-wrap: break-word;"><div style="border: 0px; clear: both; margin-bottom: 1em; padding: 0px;">
<a href="http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html" style="border: 0px; color: #0c65a5; cursor: pointer; margin: 0px; padding: 0px; text-decoration: none;">Determine</a> the JDBC URL. To connect the MySQL database using Java you need an JDBC URL in the following syntax:</div>
<pre style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin-bottom: 1em; max-height: 600px; overflow: auto; padding: 5px; width: auto; word-wrap: normal;">jdbc:mysql://hostname:port/databasename</pre>
<ul style="border: 0px; list-style: disc; margin: 0px 0px 0px 30px; padding: 0px;">
<li style="border: 0px; margin: 0px 0px 0.5em; padding: 0px; word-wrap: break-word;"><div style="border: 0px; clear: both; margin-bottom: 1em; padding: 0px;">
<code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">hostname</code>: The hostname where MySQL server is installed. If it's installed at the same machine where you run the Java code, then you can just use <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">localhost</code>. It can also be an IP address like <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">127.0.0.1</code>. If you encounter connectivity problems and using <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">127.0.0.1</code>instead of <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">localhost</code> solved it, then you've a problem in your network/DNS/hosts config.</div>
</li>
<li style="border: 0px; margin: 0px 0px 0.5em; padding: 0px; word-wrap: break-word;"><div style="border: 0px; clear: both; margin-bottom: 1em; padding: 0px;">
<code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">port</code>: The TCP/IP port where MySQL server listens on. This is by default <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">3306</code>.</div>
</li>
<li style="border: 0px; margin: 0px 0px 0.5em; padding: 0px; word-wrap: break-word;"><div style="border: 0px; clear: both; margin-bottom: 1em; padding: 0px;">
<code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">databasename</code>: The name of the database you'd like to connect to. That's <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">javabase</code>.</div>
</li>
</ul>
<div style="border: 0px; clear: both; margin-bottom: 1em; padding: 0px;">
So the final URL should look like:</div>
<pre style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin-bottom: 1em; max-height: 600px; overflow: auto; padding: 5px; width: auto; word-wrap: normal;">jdbc:mysql://localhost:3306/javabase</pre>
</li>
<li style="border: 0px; margin: 0px 0px 0.5em; padding: 0px; word-wrap: break-word;"><div style="border: 0px; clear: both; margin-bottom: 1em; padding: 0px;">
<a href="http://dev.mysql.com/doc/connector-j/en/connector-j-usagenotes-basic.html" style="border: 0px; color: #0c65a5; cursor: pointer; margin: 0px; padding: 0px; text-decoration: none;">Test the connection</a> to MySQL using Java. Create a simple Java class with a <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">main()</code> method to test the connection.</div>
<pre class="default prettyprint prettyprinted" style="background-color: #eeeeee; border: 0px; color: #393318; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin-bottom: 1em; max-height: 600px; overflow: auto; padding: 5px; width: auto; word-wrap: normal;"><code style="border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; margin: 0px; padding: 0px; white-space: inherit;">String url = "jdbc:mysql://localhost:3306/javabase";
String username = "java";
String password = "password"

System.out.println("Connecting database...");

try {
 Class.forName("com.mysql.jdbc.Driver");
 System.out.println("Driver loaded!");
} catch (ClassNotFoundException e) {
 throw new IllegalStateException("Cannot find the driver in the classpath!", e);
}</code></pre>
<div style="border: 0px; clear: both; margin-bottom: 1em; padding: 0px;">
Note that the <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">newInstance()</code> call is not needed here. It's just to fix the old and buggy <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">org.gjt.mm.mysql.Driver</code>. <a href="http://stackoverflow.com/questions/2092659/what-is-difference-between-class-forname-and-class-forname-newinstance/2093236#2093236" style="border: 0px; color: #0c65a5; cursor: pointer; margin: 0px; padding: 0px; text-decoration: none;">Explanation here</a>. If this line throws <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">ClassNotFoundException</code>, then the JAR file containing the JDBC driver class is simply not been placed in the classpath.</div>
<div style="border: 0px; clear: both; margin-bottom: 1em; padding: 0px;">
Note that you don't need to load the driver everytime before connecting. Just only once during application startup is enough.</div>
<div style="border: 0px; clear: both; margin-bottom: 1em; padding: 0px;">
If you get a <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">SQLException: Connection refused</code> or <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">Connection timed out</code> or a MySQL specific <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">CommunicationsException: 
Communications link failure</code>, then it means that the DB isn't reachable at all. This can have one or more of the following causes:</div>
<ol style="border: 0px; margin: 0px 0px 0px 30px; padding: 0px;">
<li style="border: 0px; margin: 0px 0px 0.5em; padding: 0px; word-wrap: break-word;">IP address or hostname in JDBC URL is wrong.</li>
<li style="border: 0px; margin: 0px 0px 0.5em; padding: 0px; word-wrap: break-word;">Hostname in JDBC URL is not recognized by local DNS server.</li>
<li style="border: 0px; margin: 0px 0px 0.5em; padding: 0px; word-wrap: break-word;">Port number is missing or wrong in JDBC URL.</li>
<li style="border: 0px; margin: 0px 0px 0.5em; padding: 0px; word-wrap: break-word;">DB server is down.</li>
<li style="border: 0px; margin: 0px 0px 0.5em; padding: 0px; word-wrap: break-word;">DB server doesn't accept TCP/IP connections.</li>
<li style="border: 0px; margin: 0px 0px 0.5em; padding: 0px; word-wrap: break-word;">DB server has run out of connections.</li>
<li style="border: 0px; margin: 0px 0px 0.5em; padding: 0px; word-wrap: break-word;">Something in between Java and DB is blocking connections, e.g. a firewall or proxy. </li>
</ol>
<div style="border: 0px; clear: both; margin-bottom: 1em; padding: 0px;">
To solve the one or the other, follow the following advices:</div>
<ol style="border: 0px; margin: 0px 0px 0px 30px; padding: 0px;">
<li style="border: 0px; margin: 0px 0px 0.5em; padding: 0px; word-wrap: break-word;">Verify and test them with <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">ping</code>.</li>
<li style="border: 0px; margin: 0px 0px 0.5em; padding: 0px; word-wrap: break-word;">Refresh DNS or use IP address in JDBC URL instead.</li>
<li style="border: 0px; margin: 0px 0px 0.5em; padding: 0px; word-wrap: break-word;">Verify it based on <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">my.cnf</code> of MySQL DB.</li>
<li style="border: 0px; margin: 0px 0px 0.5em; padding: 0px; word-wrap: break-word;">Start the DB.</li>
<li style="border: 0px; margin: 0px 0px 0.5em; padding: 0px; word-wrap: break-word;">Verify if mysqld is started without the <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">--skip-networking option</code>.</li>
<li style="border: 0px; margin: 0px 0px 0.5em; padding: 0px; word-wrap: break-word;">Restart the DB and fix your code accordingly that it closes connections in <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">finally</code>.</li>
<li style="border: 0px; margin: 0px 0px 0.5em; padding: 0px; word-wrap: break-word;">Disable firewall and/or configure firewall/proxy to allow/forward the port. </li>
</ol>
<div style="border: 0px; clear: both; margin-bottom: 1em; padding: 0px;">
Note that closing the <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">Connection</code> is extremely important. If you don't close connections and keep getting a lot of them in a short time, then the database may run out of connections and your application may break. Always acquire the <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">Connection</code> in a <a href="https://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html" style="border: 0px; color: #0c65a5; cursor: pointer; margin: 0px; padding: 0px; text-decoration: none;"><code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">try-with-resources</code> statement</a>. Or if you're not on Java 7 yet, explicitly close it in <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">finally</code> of a <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">try-finally</code> block. Closing in <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">finally</code> is just to ensure that it get closed as well in case of an exception. This also applies to <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">Statement</code>, <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">PreparedStatement</code> and <code style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin: 0px; padding: 1px 5px; white-space: pre-wrap;">ResultSet</code>.</div>
</li>
</ol>
<div>
 </div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjgjbl7NRKBlTRCEmdGuKLygVzJ8uH4HpBUHL4kURhTk4m6VwA4tO8t3J21Q0SxNVRIdqGh8Zfq7qrSqMx2TPvXZ_oEph9Z0LNjJs7IBuOsDG1PMReUHw_aT35SjSLLlD8s2IWQ3VuXEQKN/s1600/DB2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjgjbl7NRKBlTRCEmdGuKLygVzJ8uH4HpBUHL4kURhTk4m6VwA4tO8t3J21Q0SxNVRIdqGh8Zfq7qrSqMx2TPvXZ_oEph9Z0LNjJs7IBuOsDG1PMReUHw_aT35SjSLLlD8s2IWQ3VuXEQKN/s1600/DB2.png" /></a></div>
<div>
 </div>
</div>

Here's a step by step explanation how to install MySQL and JDBC and how to use it

Download and install the MySQL server . Just do it the usual way. Remember the port number whenever you've changed it. It's by d...

+ Read more »

MySQL With JDBC Driver With Example

<div dir="ltr" style="text-align: left;" trbidi="on">
Here’s an example to show you how to connect to MySQL database via a JDBC driver. First, get a MySQL JDBC driver from here -<a href="http://dev.mysql.com/downloads/connector/j/" style="background-color: white; color: #0f3b68; font-family: Arial, sans-serif; font-size: 14px; line-height: 21px;" target="_blank">MySQL JDBC Driver Download Here</a>. 
 
Java JDBC connection example 
<div style="background-color: white; color: #3c3c3c; font-family: Arial, sans-serif; font-size: 14px; line-height: 21px; margin-bottom: 20px;">
Code snippets to use a JDBC driver to connect a MySQL database.</div>
<div class="wp_syntax" style="background-color: white; color: #3c3c3c; font-family: Arial, sans-serif; font-size: 14px; line-height: 21px;">
<div class="code">
<div class="separator" style="clear: both; text-align: center;">
 </div>
<pre class="java" style="background-color: #f7f7f9; border-bottom-left-radius: 4px; border-bottom-right-radius: 4px; border-top-left-radius: 0px; border-top-right-radius: 0px; border: 1px solid rgb(209, 209, 232); color: #333333; font-size: 13px; line-height: 1.5; margin-bottom: 20px; overflow-x: auto; padding: 10px;">Class.forName("com.mysql.jdbc.Driver");
Connection conn = null;
conn = DriverManager.getConnection("jdbc:mysql://hostname:port/dbname","username", "password");
conn.close();</pre>
</div>
</div>
<div style="background-color: white; color: #3c3c3c; font-family: Arial, sans-serif; font-size: 14px; line-height: 21px; margin-bottom: 20px;">
See a complete example below :</div>
<pre class="java" style="border-bottom-left-radius: 4px; border-bottom-right-radius: 4px; border-top-left-radius: 0px; border-top-right-radius: 0px; border: 1px solid rgb(209, 209, 232); color: #333333; font-size: 13px; line-height: 1.5; margin-bottom: 20px; overflow-x: auto; padding: 10px;">package com.mani.common;
 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
 
public class JDBCExample {
 
 public static void main(String[] argv) {
 
 System.out.println("-------- MySQL JDBC Connection Testing ------------");
 
 try {
 Class.forName("com.mysql.jdbc.Driver");
 } catch (ClassNotFoundException e) {
 System.out.println("Where is your MySQL JDBC Driver?");
 e.printStackTrace();
 return;
 }
 
 System.out.println("MySQL JDBC Driver Registered!");
 Connection connection = null;
 
 try {
 connection = DriverManager
 .getConnection("jdbc:mysql://localhost:3306/mani","root", "password");
 
 } catch (SQLException e) {
 System.out.println("Connection Failed! Check output console");
 e.printStackTrace();
 return;
 }
 
 if (connection != null) {
 System.out.println("You made it, take control your database now!");
 } else {
 System.out.println("Failed to make connection!");
 }
 }
}</pre>
<h4 style="background-color: white; color: #3c3c3c; font-family: Arial, sans-serif; font-size: 22px; line-height: 36px; margin: 50px 0px 4px; text-rendering: optimizelegibility;">
Run it</h4>
<div style="background-color: white; color: #3c3c3c; font-family: Arial, sans-serif; font-size: 14px; line-height: 21px; margin-bottom: 20px;">
Assume <code style="background-color: #f7f7f9; border-bottom-left-radius: 3px; border-bottom-right-radius: 3px; border-top-left-radius: 3px; border-top-right-radius: 3px; border: 1px solid rgb(225, 225, 232); color: #0f3b68; font-family: Monaco, Menlo, Consolas, 'Courier New', monospace; font-size: 12px; padding: 4px;">JDBCExample.java</code> is store in <code style="background-color: #f7f7f9; border-bottom-left-radius: 3px; border-bottom-right-radius: 3px; border-top-left-radius: 3px; border-top-right-radius: 3px; border: 1px solid rgb(225, 225, 232); color: #0f3b68; font-family: Monaco, Menlo, Consolas, 'Courier New', monospace; font-size: 12px; padding: 4px;">c:\test</code> folder, along with the <code style="background-color: #f7f7f9; border-bottom-left-radius: 3px; border-bottom-right-radius: 3px; border-top-left-radius: 3px; border-top-right-radius: 3px; border: 1px solid rgb(225, 225, 232); color: #0f3b68; font-family: Monaco, Menlo, Consolas, 'Courier New', monospace; font-size: 12px; padding: 4px;">MySQL JDBC driver</code></div>
<div class="wp_syntax" style="background-color: white; color: #3c3c3c; font-family: Arial, sans-serif; font-size: 14px; line-height: 21px;">
<div class="code">
<pre class="bash" style="background-color: #f7f7f9; border-bottom-left-radius: 4px; border-bottom-right-radius: 4px; border-top-left-radius: 0px; border-top-right-radius: 0px; border: 1px solid rgb(209, 209, 232); color: #333333; font-size: 13px; line-height: 1.5; margin-bottom: 20px; overflow-x: auto; padding: 10px;">C:\test>java -cp c:\test\mysql-connector-java-5.1.8-bin.jar;c:\test JDBCExample
-------- MySQL JDBC Connection Testing ------------
MySQL JDBC Driver Registered!
You made it, take control your database now!
 
C:\test></pre>
</div>
</div>
<div style="background-color: white; color: #3c3c3c; font-family: Arial, sans-serif; font-size: 14px; line-height: 21px; margin-bottom: 20px;">
P.S To run this example, your need <code style="background-color: #f7f7f9; border-bottom-left-radius: 3px; border-bottom-right-radius: 3px; border-top-left-radius: 3px; border-top-right-radius: 3px; border: 1px solid rgb(225, 225, 232); color: #0f3b68; font-family: Monaco, Menlo, Consolas, 'Courier New', monospace; font-size: 12px; padding: 4px;">mysql-connector-java-{version}-bin.jar</code> in your classpath.</div>
<div style="background-color: white; color: #3c3c3c; font-family: Arial, sans-serif; font-size: 14px; line-height: 21px; margin-bottom: 20px;">
Thats it !! 
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjLJWlZ3B9L61qPOYM0bBZ8M_JjgraZJm4FruXTJG4MgJpNsa3DTvX-PL5FqBfXoQIiRrxBOBID3l2q9OwsUvsvYCkxSs6eL_bcWDVz9oDoMidCCn6mp1OTS-DlK4M2HzRJ4_URnTujjvtc/s1600/DB.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjLJWlZ3B9L61qPOYM0bBZ8M_JjgraZJm4FruXTJG4MgJpNsa3DTvX-PL5FqBfXoQIiRrxBOBID3l2q9OwsUvsvYCkxSs6eL_bcWDVz9oDoMidCCn6mp1OTS-DlK4M2HzRJ4_URnTujjvtc/s1600/DB.png" /></a></div>
</div>
</div>

MySQL With JDBC Driver With Example

Here’s an example to show you how to connect to MySQL database via a JDBC driver. First, get a MySQL JDBC driver from here - MySQL JDBC Dri...

+ Read more »

Top 10 JDBC Best Practices for Java Programmer

<div dir="ltr" style="text-align: left;" trbidi="on">
JDBC Best Practice #1: Use PreparedStatement 
This is by far most popular JDBC practices suggested by everyone who has worked in JDBC API in Java. Indeed PreparedStatement deserve that admiration because of useful services it provides like prevention from SQL injection, Precompiled SQL queries and use of bind variables as discussed in  why Use PreparedStatement in Java 
 
JDBC Best Practice #2: Use ConnectionPool 
ConnectionPool as JDBC best practice has already gained recognition and it even become standard now days. Several framework provides in built connection Pool facility like Database Connection Pool in Spring, DBCP and if you are running in managed environment like J2EE Application Server e.g. WAS or JBOSS, Server will provide Connection Pool facility. rational behind this JDBC best practices is that Creating JDBC connection take relatively longer time which can increase overall response time, by caching JDBC connection in pool application can immediately access database. 
 
JDBC Best Practice #3: Disable auto commit mode 
This is one of those JDBC best practices which provided substantial performance gain in our JDBC batch update example. Its recommended to run SQL query with auto commit mode disable. Rational behind this JDBC best practice is that with auto commit mode disabled you can group SQL Statement in one transaction while in case of auto commit mode every SQL statement runs in its own transaction and committed as soon as it finishes. So always run queries with auto commit mode disabled 
 
JDBC Best Practice #4: Use JDBC Batch Update 
This is another JDBC best practice which is very popular. JDBC API provides addBatch() method to add SQL queries into batch and executeBatch() to send batch queries for execution. Rational behind this JDBC best practices is that, JDBC batch update potentially reduce number of database roundtrip which result in significant performance gain. So always Use JDBC batch update for insertion and update queries. 
 
JDBC Best Practice #5: Access ResultSet using column name to avoid invalidColumIndexError 
JDBC API allows to access data returned by SELECT query using ResultSet, which can further be accessed using either column name or column index. This JDBC best practice suggest using column name over column index in order to avoid InvalidColumnIndexException which comes if index of column is incorrect, most common of them is 0, since ResultSet column Index starts from 1, zero is invalid. Also you don't need to change your JDBC access code if order of column changed in SELECT SQL query, which is a major maintenance gain and a robust way to write JDBC code. Some Java programmer may argue that accessing column using index is faster than name, which is true but if you look in terms of maintenance, robustness and readability, I prefer accessing column using name in ResultSet Iterator. 
 
JDBC Best Practice #6: Use Bind variables instead of String concatenation 
In JDBC Best Practice #1 we have suggest to use PreparedStatement in Java because of better performance. But performance can only be improved if you use bind variables denoted by ? or place holders. which allows database to run same query with different parameter. This JDBC best practices also result in better performance and also provide protection against SQL injection. 
 
JDBC Best Practice #7: Always close Statement, PreparedStatement and Connection. 
Nothing new on this JDBC Best practice. Its common Java coding practice to close any resource in finally block as soon as you are done with that. JDBC Connection and other JDBC classes are costly resource and should be closed in finally block to ensure release of connection even in case of any SQLException. From Java 7 onwards you can use Automatic Resource Management (ARM) Block to close resources automatically. 
 
JDBC Best Practice #8: Choose suitable JDBC driver for your application 
There are 4 typs of JDBC driver in Java and it can directly affect the performance of DAO layer. always use latest JDBC Driver if available and prefer type 4 native JDBC Drivers. 
 
JDBC Best Practice #9: Use standard SQL statement and avoid using db specific query until necessary 
This is another JDBC best practice in Java which ensures writing portable code. Since most of JDBC code is filled up with SQL query its easy to start using Database specific feature which may present in MySQL but not in Oracle etc. By using ANSI SQL or by not using DB specific SQL you ensure minimal change in your DAO layer in case you switch to another database. 
 
JDBC Best Practice #10: Use correct getXXX() method 
This is the last JDBC best practice in this article which suggest using correct getter while getting data from ResultSet to avoid data conversion even though JDBC allows to get any data type using getString()or getObject(). 
 
That's all on JDBC best practices for Java Programmer, I am sure there are many more JDBC best practices around but these are most common practices which I can think of. let us know if you are familiar with any other JDBC best practice. 
 
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjXmF9I_U0FlFVzwkrcb1Mtkt-5nWWYaRNkHgl08_EDq7EVYQ3iwgRce9etJGx_Iit2vxOzyWTfI2MvMHgvvYY9Ekemwjl3ybcZ35XqXrQfN_YIuJohyphenhyphenz1N7OlndLjNCdioTCyuaV6oYLc9/s1600/DB2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjXmF9I_U0FlFVzwkrcb1Mtkt-5nWWYaRNkHgl08_EDq7EVYQ3iwgRce9etJGx_Iit2vxOzyWTfI2MvMHgvvYY9Ekemwjl3ybcZ35XqXrQfN_YIuJohyphenhyphenz1N7OlndLjNCdioTCyuaV6oYLc9/s1600/DB2.png" /></a></div>
 </div>

Top 10 JDBC Best Practices for Java Programmer

JDBC Best Practice #1: Use PreparedStatement This is by far most popular JDBC practices suggested by everyone who has worked in JDBC API i...

+ Read more »

Subscribe to: Posts ( Atom )