Posts in the “jdbc” category

Case insensitive SQL SELECT query FAQ: How do I issue SQL SELECT queries while ignoring case (ignoring whether a string is uppercase or lowercase)?

[toc]

Java JDBC FAQ: Can you share Java JDBC connection string examples for the most popular relational databases?

Some days we all need something simple, and today I needed the example syntax for a JDBC connection string (the JDBC URL) for MySQL and Postgresql databases. While I was digging through my old examples, I found JDBC connection string examples for other databases, and thought I'd share them all here.

To that end, here are some example Java JDBC connection string examples for various databases, including MySQL, Postgres, SQL Server, and DB2.

JDBC connection string examples

Here’s a table showing the syntax for JDBC URLs and drivers that I've used on recent projects.

Java PreparedStatement FAQ: Can you share an example of a Java PreparedStatement that executes a SQL INSERT query?

Yes ... I just realized I don't have a Java PreparedStatement INSERT example out here, so ... (searching, searching) ... here you go. Here's the source code for a Java/JDBC PreparedStatement "INSERT" query example:

Here's a quick example of how to use a JDBC PreparedStatement with an SQL SELECT query when accessing a database. To make it a little more complicated I added a LIKE clause to the SELECT statement. So, this is really an example of a PreparedStatement that uses a SELECT query that uses a LIKE clause.

Java/JDBC try/catch/finally exception FAQ: Can you show me a decent example of how to catch a JDBC exception in a try/catch/finally block?

Beauty is in the eye of the beholder, but here's a JDBC example showing some Java code that uses a try/catch/finally block with a Java JDBC query:

Spring JDBC FAQ: How do I retrieve the auto-generated database key for a serial field (also known as an auto_increment field in MySQL) after I perform a SQL INSERT using Spring JDBC?

Java MySQL Driver FAQ: Can you share a Java/MySQL JDBC Driver and URL example, i.e., how to connect to MySQL in Java?

Sure. Here's a quick post to help anyone that needs a quick MySQL JDBC Driver and URL reference.

The basic MySQL JDBC Driver and Java MySQL URL information you need is shown here:

Java/JDBC connection FAQ: How do I connect to a database using Java and JDBC?

Let's take a look at a JDBC database connection example. In this example I'll connect to a Postgresql database, but as you'll see from the code and other examples on this web site, the steps are always very similar.

A Java/JDBC Postgresql database connection example

To get a JDBC connection to a PostgreSQL database do this:

I don't know if this Java/JDBC sample program will help anyone, but I thought I'd share it here. It's a variation of a program I use to connect to a JDBC database (in this case a Postgresql database) whenever I need to look at some information. In this particular case I wrote the program because I didn't have access to the psql command-line tool, so I created this Java program, compiled it, and ran it to see what I wanted to see.

Note: You can use the Java program below for this task, especially if you’re not using MySQL, but if you are using MySQL, you can use this query instead:

Here's a quick example of how to set a default value for an HSQLDB TIMESTAMP field:

create cached table directories (
  dir_id identity NOT NULL,
  directory varchar(255) NOT NULL,
  time timestamp default 'now'
);

There are other ways to do this, but the important line that sets the default timestamp in the above SQL is this:

time timestamp default 'now'

This is where I'm creating a timestamp field named "time" that automatically defaults to the current date/time when a record is created.

A Spring JDBC SELECT and INSERT example: Here's the source code for a complete Spring DAO class from a project that I'm currently working on (a Java-based web interface to the open source Nagios project) that shows how to use a few Spring JDBC methods, including both a SELECT example and a simple INSERT example.

I'll add more examples to this site later (and with more introduction/description), but for now I'm just going to drop this Java class sample out here and hope that it helps you "Learn Spring JDBC by example".

Spring JDBC DELETE FAQ: Can you provide some examples of using SQL DELETE queries using Spring JDBC (Spring Dao classes and methods)?

Here are a few Spring Dao examples, specifically JDBC/SQL DELETE examples.

Spring JDBC delete example #1: Delete a record by the primary key

In this first Spring JDBC DELETE example, I pass in an id parameter, which represents the primary key in my hosts database table. This JDBC/SQL statement deletes the record from that table that matches that id field.

Here's a quick post to help anyone that needs a quick JDBC Driver and URL reference when using Postgresql (Postgres) with Java (and JDBC).

The basic Postgresql JDBC Driver and URL information you need is shown here:

Here's a quick post to help anyone that needs a quick JDBC Driver and URL reference when using Microsoft SQL Server with Java and JDBC.

The basic SQL Server JDBC Driver and URL information you need is shown here:

Here's a quick post to help anyone that needs a quick Driver and URL reference when using DB2 with Java and JDBC.

If all you need is a reference to the DB2 JDBC Driver and URL connection string syntax, this basic information should work:

I just finished a short tutorial on using an open source JDBC framework named SQLProcessor. The SQLProcessor is a facade for some of the JDBC API. It's very cool on a variety of levels, including reducing coding errors, simplifying your Java code, and providing great debug output. The tutorial is available here.