Today is Sunday, 10th March 2019

Archive for March, 2014


Java JDBC Tutorial – Part 2: Insert data into a Database

In this video tutorial, I will show you how to insert data into a database with Java JDBC.

Please subscribe 🙂

 

Video Transcript

Time – 00:00
Hello. In this video, we’re going to learn how to use Java JDBC to insert data into a database.

Time – 00:10
As for as the prep work that you need to have taken care of, you need to make sure that you have a database already installed. In this example, we’re going to use MySQL. Also, you need to make sure you have the JDBC driver installed for your database. In this example, we’re going to make use of a very simple table called employees. This table is going to have four columns. It’s going to have the ID, the last name, the first name and the email address.

Time – 00:44
Now, let’s take a look at this table in our SQL query tool. In this example, I have the employees table. I run and select star against that table, and I have my output. We have three employees in the database right now: John Doe, Mary Public and Susan Queue. Now, if we wanted to actually insert data into the database, we could use the query tool and we could use the statement here. “insert into employees”, specify the columns, last name, first name, email, and then the actual values.

Time – 01:26
What we’re going to do is take this information and actually use it in a Java program. Our basic development process is, first, getting a connection to a database. Second, creating a statement, and third, executing the SQL insert. Let’s move over to our Java development tool.

Time – 01:51
In this application, it’s a very simple project. What we need to do first is get a connection to a database. We’ll have Driver Manager get connection. We give the URL, the user ID and the password. Once we have a connection to the database, then we can create a statement.

Time – 02:16
I’ll say, “myConn.createStatement” and it gives me a blank statement object. Now that I have a statement, I can setup my SQL. The SQL, I will assign it to a string variable. It’s the same SQL we saw in the previous slide. “Insert into employees”, I give the last name, first name, email, and then I’ll also provide the values: Brown for last name, David, and then that person’s email address. Now that I have the SQL, I make use of the statement here, “mystmt.executeupdate” and I pass in the SQL. This will actually execute a SQL code against the database.

Time – 03:03
Then when I’m finished, I’ll just do a system out print line, “Insert Complete”, and then I have my normal exception handler for the code. Let’s go ahead and save this and let’s run it as a Java application.

Time – 03:18
I’ll say execute. Note at the bottom it says, “Insert complete.” Now we can go through and actually check this against our database. If I run the query one more time, and now I can see the statement or the new entry here for our new employee, davidbrown@foo.com. We were successful.

Time – 03:48
Let’s go ahead and wrap up. We were able to insert data into a database using Java JDBC. If you’d like the code examples or more video tutorials, then visit luv2code.com.

Source Code: Driver.java


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Driver {

	public static void main(String[] args) throws SQLException {

		String url = "jdbc:mysql://localhost:3306/demo";
		String user = "student";
		String password = "student";

		Connection myConn = null;
		Statement myStmt = null;

		try {
			// 1. Get a connection to database
			myConn = DriverManager.getConnection(url, user, password);

			// 2. Create a statement
			myStmt = myConn.createStatement();

			// 3. Execute SQL query
			String sql = "insert into employees " + " (last_name, first_name, email)"
					+ " values ('Brown', 'David', 'david.brown@foo.com')";

			myStmt.executeUpdate(sql);

			System.out.println("Insert complete.");
		} catch (Exception exc) {
			exc.printStackTrace();
		} finally {
			if (myStmt != null) {
				myStmt.close();
			}

			if (myConn != null) {
				myConn.close();
			}
		}
	}
}

MySQL Script for Creating the Database table


create database if not exists demo;

use demo;

drop table if exists employees;

CREATE TABLE <code>employees</code> (
<code>id</code> int(11) NOT NULL AUTO_INCREMENT,
<code>last_name</code> varchar(64) DEFAULT NULL,
<code>first_name</code> varchar(64) DEFAULT NULL,
<code>email</code> varchar(64) DEFAULT NULL,
PRIMARY KEY (<code>id</code>)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

INSERT INTO <code>employees</code> (<code>id</code>,<code>last_name</code>,<code>first_name</code>,<code>email</code>) VALUES (1,'Doe','John','john.doe@foo.com');
INSERT INTO <code>employees</code> (<code>id</code>,<code>last_name</code>,<code>first_name</code>,<code>email</code>) VALUES (2,'Public','Mary','mary.public@foo.com');
INSERT INTO <code>employees</code> (<code>id</code>,<code>last_name</code>,<code>first_name</code>,<code>email</code>) VALUES (3,'Queue','Susan','susan.queue@foo.com');

Download Source Code: jdbcdemo-insert.zip



Java JDBC Tutorial – Part 1: Connect to MySQL database with Java JDBC

In this video tutorial, you will learn how to connect to a MySQL database with Java JDBC.

Please subscribe 🙂

 

Download Java Source Code


SQL Script

The code examples in the tutorials make use of the following user:

  • user id: student
  • password: student

You will need to create this user in your MySQL Admin tool. Execute the following SQL in the MySQL Admin tool:


You will need to create the database table. Run the following SQL script in your MySQL admin tool.

 


Video Transcript

Time – 00:00
Hello. In this session we’re going to learn how to connect a MySQL database with Java. Before we get started there are some prep work that needs to take place. One is that you need to have a MySQL database already installed. If not, you can download one from the site listed here on the slide.

Time – 00:20
Next, we’ll need to download and install a MySQL JDBC driver. This driver will allow the Java program to connect to the database. You can download it from this URL, and I’ll do that now. I’ll just take this URL, drop it into my favorite browser and here I am at the page. I’m on this page, this is for downloading the Connector/J browser. I’ll select the platform, platform independent. Now move down and I’ll select download and it will download a zip archive. Make sure you download the most recent version. In this case it’s 5.1.29. Make sure you download something at that version or later.

Time – 01:04
Download, scroll down, select, “No thanks, just start my download.” This will start downloading the file for you. Now what we can do is show it in the finder. I’ll double click it to extract it. At this point I have the driver extracted. There’s a file in here called MySQL connector.jar that we’ll use later.

Time – 01:34
Next, what I need to do is create a Java project in Eclipse. Let’s do that. On Eclipse I’ll say I’ll save file, new, Java project. This will be JDBC demo and I will select finish. What I’ll do is I will add a new class. New, class, this will be my driver and it will have a public static void main region. At this point we have a shell for our project created.

Time – 02:28
Next, what we’ll do is we will talk to the actual development process and I really like this slide because it outlines exactly what we need to do. The first thing we’ll do is add the database driver to the classpath. Next, we’ll get a connection, we’ll submit a SQL query and then we’ll process the result set.

Time – 02:48
Let’s start with step one, adding a MySQL database driver to the classpath. In our Eclipse project I’m going to add a new folder called lib, lib for library. In this lib folder I’m going to add the driver that I downloaded earlier. What I’ll do is I’ll just grab this driver. It’s the MySQL connector bin.jar and I’ll just drag and drop it to this lib folder. It will prompt me and I’ll say go ahead and copy the file.

Time – 03:25
Next, I need to add the jar file, the driver to the classpath. I’ve set this up with properties, choose Java build path and then I’ll choose add jars. Expand this folder, that lib, I’ll grab that jar file and I’ll have it setup. Great.

Time – 03:51
Now we can start coding. We need to get a connection, submit a SQL query and process a result set. Let me just sketch this out on a slide over here. The first thing I’ll do up top is I’ll just do an import on java.sql.* because all of the classes and interfaces that we need are in this package.

Time –  04:17
I’ll also just setup a try catch block just to handle for any exception that may occur. Small typo there.

Time – 04:40
Inside of this section I’m going to first get a connection to database. Then next I will create a statement. Then from here I will execute a SQL query and then I will process the result set.
Time – 05:16
That’s my basic game plan, let’s fill it in with some real code. Here I’ll say connection, myConn = DriverManager.getConnection. I’ll give the jdbc:mysql://localhost:3306/demo student, student. Student is the user ID and student is also the password.

Time – 06:00
Now, let me digress for one second here. We’re connecting to a database called … it’s on local host, demo. On my system, I already have MySQL installed. I’m using a MySQL workbench, it allows me to query or look at the actual database. I have a database out here called demo and I have a table called employees. This employees table has three columns. It has a column for the ID, the last name, first name and the e-mail address for that employee.

Time – 06:42
I’m going to want to do something very similar here in my actual Java code. The big things that I have are database called demo and a table called employees. That gives me the portion here of connecting to this database demo and then now I can go ahead and start writing some of MySQL statements.

Time – 07:00
First up I’ll start with statement, my statement equals myConn.createstatement. Then I execute the SQL query. I’ll say result set myRs equals my statement.executeQuery, select star from employees.

Time – 07:41
Next I’ll process the result set, myRs.next then I’ll say system.out.printline and I will print line myRs.getString. I’m going to retrieve the person’s last name and then I’ll also say … I’ll append this with myRs.getString first name and I’ll continue the loop.

Time – 08:20
That’s pretty much it as far as the actual coding. Now, let’s try and run this application and see how things work out for us. We can all just right click and I’ll say run as Java application. Then note, the output here, John Doe, Mary Public, Susan Queue, and if we look at our database same values occur. We are successful in connecting to MySQL database with Java.

Time – 08:58
That’s it, thank you for watching the presentation. You can visit luv2code.com for more tutorials.

 

Source Code: Driver.java

Download Source Code



Programming is fun!

There was a nice article posted on DZone. In the article, they interviewed Henrik Warne, who is a developer based in Sweden. He stated that he enjoyed programming since his early days of working on his Vic-20 (way cool!). The best part of article is a quote that he gave from Fred Brooks, author of “The Mythical Man-Month“.

I think Fred Brooks said this very well when he wrote about why programming is fun in the beginning of the book “The Mythical Man-Month”:

“Finally, there is the delight of working in such a tractable medium. The programmer, like the poet, works only slightly removed from pure thought-stuff. He builds his castles in the air, from air, creating by the exertion of the imagination. Few media of creation are so flexible, so easy to polish and rework, so readily capable of realizing grand conceptual structures. Yet the program construct, unlike the poet’s words, is real in the sense that it moves and works, producing visible outputs separate from the construct itself.”

Now that’s why I luv2code 🙂