Java JDBC Tutorial – Part 3: Updating Data in a MySQL Database

Java JDBC Tutorial – Part 3: Updating Data in a MySQL Database


Hello. In this video, you will learn how to
use Java JDBC to update data in a database. In regards to prep work, you must have a database
installed. This demo will use MYSQL. You also need to have the database driver configured.
Again, we’ll use MYSQL. If you need help on how to do this, please look at my other video,
Connecting To MYSQL Databases with Java. In this example, we will use the Employees
table. This table has columns for employee ID, last name, first name and email address.
Let’s switch over to the MYSQL tool, so we can take a look at some of the data. Here’s our sample table that we have with
sample data already in place. What I’d like for you to do is make note of David Brown’s
email address. He is currently listed as [email protected] We’re going to change this email address to
make use of luv2code.com. His new email address is [email protected]
The following sql can perform this update. We will use this sql update statement in a
Java application. This slide outlines our Java development process.
First, we will get a connection to the database. Then we will create a statement. Finally,
we will execute the sql update statement. Let’s move onto our development environment
and get started. We’re going to start with a simple Java program. The program is going
to start out with the url, the connection to the database, the user ID and password. The first thing that we’ll do is we will get
a connection to the database. Then we will create a statement. Then we’ll actually execute
the sql query. This is the same query that we had from the previous slide. Then we’ll actually do an execute update.
This will actually send the query to the database and perform the update. Finally, just to give
us some debugging information, we’ll have a print-line statement to say update complete. Let’s go ahead and run this application. Run
as Java application. It says update complete. Great. Let’s go to our SQL tool to verify
the results. In the SQL tool, I just simply do a refresh
and note the new email address for David Brown. It’s now [email protected] That works out,
and we’re actually successful with updating data using Java JDBC. This wraps up the presentation. If you’d like
to download the source code, please visit the website luv2code.com.

Danny Hutson

29 thoughts on “Java JDBC Tutorial – Part 3: Updating Data in a MySQL Database

  1. Great – I am currently in my apprenticeship and learning java.. I really like to view your Videos about the JDBC stuff, since I need to learn this at the moment 🙂

    Is there any chance (Sorry if I am too blind to see it) that you have videos about meta data in databases?
    Greetings,
    Søren

  2. Thanks, you're doing a great job.
    Java JDBC Tutorial – Part 3: Updating Data in a MySQL Database

  3. Ten minutes of your tutorials teached me more then listening to my professor at university for six months. Really good tutorials dude!

  4. I endorse Vertex Techno words!!!!!

    Is there the possibility to get the pdf file from all your MySql classes?

  5. Is there any way to use a parameter inside the statement. For example, UPDATE `some table` SET `column` = column + more stuff WHERE `Level Name` = ?

  6. Hello chad,
    While I run this code:

    package jdbcdemo;

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

    public class Update {

    public static void main(String[] args) {

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

    try{
    Connection myConn = DriverManager.getConnection(url, user, pass);
    Statement myStmt = myConn.createStatement();

    String sql = "update employees"
    + "set email ='[email protected]' "
    + "where id=15";
    myStmt.executeUpdate(sql);
    System.out.println("Update Complete..");
    }
    catch(Exception exc){
    exc.printStackTrace();
    }

    }

    }

    Then it shows this message in output:
     
    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '='[email protected]' where id=15' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
    at com.mysql.jdbc.Util.getInstance(Util.java:387)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2547)
    at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1541)
    at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2605)
    at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1469)
    at jdbcdemo.Update.main(Update.java:22)

    So do u have any solution for that…

  7. hi ur videos r awesome but i need a little help. i made user record using ms access in java. after inserted deleted or updating the data, data will not show in window. what i can do this is my first project plz help me

  8. Hello, your instructions were very clear.

    I was trying to implement a project using the above concept. I wanted to add the output from a command "netstat -b" into the database using jdbc, but I was not able to implement it. I kept getting errors. Can you please tell me what's wrong with the code:

    public class Netstatb {

    public static void main(String[] args) {
    String proto, local_Address, foreign_Address, state;
    final String cmd = "netstat -b";

    try {
    Process process = Runtime.getRuntime().exec(cmd);
    BufferedReader br = new BufferedReader(new InputStreamReader(process.getInputStream()));
    //String line;
    System.out.println(br.readLine());
    while (br.readLine() != null){
    //String temp = br.readLine().trim();
    if ((br.readLine().trim()).contains("Active Connections")) {

    }
    else if (br.readLine().trim().contains("Proto")) {

    } else if (br.readLine().trim().isEmpty()) {

    }
    else {
    List <String> output = Arrays.asList(br.readLine().trim().split("\s++"));

    proto = output.get(0);
    local_Address = output.get(1);
    foreign_Address = output.get(2);
    state = output.get(3);
    new Netstatb().SQLRun(proto, local_Address, foreign_Address, state);
    }
    }
    process.waitFor();
    br.close();
    System.exit(0);

    }catch(Exception exc) {
    exc.printStackTrace();
    }
    }

    public void SQLRun(String proto, String local_Address, String foreign_Address, String state) {
    try {
    //1.Get a connections to database
    Connection newConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/nets", "root", "" );

    //Create statement object
    //Statement newStmt1 = newConn.createStatement();

    /*String CreateTable = "CREATE table netstat"
    + "(Proto varchar(50),"
    + "Local_Address varchar(50),"
    + "Foreign_Address(80),"
    + "State);";*/

    String insert = "INSERT into netstatb values" +
    "( " + proto + ", " + local_Address + ", "
    + foreign_Address + ", " + state + " );";

    Statement newStmt2 = null;
    try {//Execute SQL query
    //newStmt1.executeUpdate(CreateTable);
    newStmt2 = newConn.createStatement();
    newStmt2.executeUpdate(insert);

    }
    finally {
    if (newStmt2 != null) {
    newStmt2.close();
    }
    }

    }catch(Exception exc) {
    exc.printStackTrace();
    }

    }

    }

  9. Hi sir , when I download the same connector it isn't the same as you . Do you have any command to say ? I downloaded the same software from the same website , but I dont know what is the problem .

  10. Hello sir ,
    I have a problem in my project –
    How to Display a calendar with some block dates in it?
    Block dates according to the database(mysql database)
    and , for each date of calendar , there should be a drop down of flights available for that flight.
    for example ,
    an employee 'Neha' login to air hostess mgmt system.
    then for her there are flights on some date.
    she select 01jul-2016, then she will see list of available flight for that selected date.
    if there are no flight then she will see a red cross on that date.

  11. Your videos are amazing and so informative.
    i have question regarding the database mysql. if i export a java application as jar file, does the database itself export with the jar file. in other word, does the application work on any kind of pc and still can connect to the database ?

  12. package pack1;
    import java.sql.*;
    public class Test {
    public static void main(String[] args) {
    String url="jdbc:mysql://localhost:3306/sqldatabase";
    String username="root";
    String password="12345";
    try{
    // Class.forName("com.mysql.jdbc.Driver");
    Connection con=DriverManager.getConnection(url,username,password);
    Statement stmt=con.createStatement();
    String sql="update table1 "+"set place='sourya'"+" where name=deep";
    stmt.executeUpdate(sql);
    System.out.println("update completed");
    con.close();
    }catch(Exception e){ System.out.println(e);}
    }

    }
    Where is the problem?.

  13. Hello sir,
    your videos are very helpful for me. I learned much from it 🙂

    By the way, you have an error in your SQL syntax in the jdbcdemo-update.zip data.
    The error is at the row 26: String sql = "update employees set email='[email protected] where id=9";
    It should be: String sql = "update employees set email='[email protected]' where id=9";
    Only <'> is missing after <… com>

    Thank you for the nice turtorial!

  14. com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= '[email protected]'where id = 3' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486)
    at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1552)
    at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2607)
    at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1480)
    at jdemo.Update.main(Update.java:18)

  15. Statement stmt = con.create Statement();
    String query = "alter table personinfo add email varchar(50)";
    stmt.executeUpdate(query);

    i added new column to my table and it worked….. i now want to know how to insert into multiple rows using a single query …
    as i already did this
    Statement stmt = conn.createStatement();

    String query = "update personinfo set email='[email protected]' where Id=2";
    stmt.executeUpdate(query);
    System.out.println("value Added");

    this also works but need to know how to do it in a single query for different Id's with different email's

  16. Really helpful set of tutorials! The only thing that I don’t is how to make check if the user/record exists in the database ?

Leave a Reply

Your email address will not be published. Required fields are marked *