Java jdbc records from console to database java.sql.sqlexception:

Question

1.00/5 (1 vote)

See more:

Code for values that are taken from user and inserted into database but gives error

"java.sql.SQLException: The column position '45' is out of range. The number of columns for this ResultSet is '2'."

when user enters 45 as number, the code takes that as column number which is 2
what must be changed so that number is the literal that goes in the column id the table has two columns named ID and Name;

package Rdbms;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

class Jdbcfrnd {

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

		String drv = "org.apache.derby.jdbc.ClientDriver";
		String url = "jdbc:derby://localhost:1527/Oracle;create=true;";
		String user = "Android";
		String password = "java";

		Connection myConn = null;
		PreparedStatement myStmt = null;
		Connection dbConnection = null;

		Scanner scanner = null;

		try {
			// 0. read user input from command line: last name, first name and email
			scanner = new Scanner(System.in);
                        
                        Scanner sc = new Scanner(System.in);
                        System.out.print("Enter number 1: ");
                        int a;
                        a = sc.nextInt();                        			                                                

			System.out.print("Enter your Name: ");
			String firstName = scanner.nextLine();

                        //System.out.print("Enter your email: ");
                        //String email = scanner.nextLine();

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

			// 2. Create a statement
			String sql = "insert into GOOD "
					+ " (ID, Name)" + " values (?, ?)";

			myStmt = myConn.prepareStatement(sql);

			// set param values
			myStmt.setString(a , firstName);
			//myStmt.setString(ID, "Android");

			// 3. Execute SQL query
			myStmt.executeUpdate();

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

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

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

}

What I have tried:

altered the code and database structure

Posted 31-Jan-18 3:45am

four systems

Updated 1-Feb-18 2:52am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Answer 1 · 2018-01-31T06:35:00

Solution 1

You are using the input number, instead of the column index in your SetString statement. It should be something like:

Java

myStmt.setInt(1 , a);    // column 1 is the ID value
myStmt.setString(2 , firstName);  // column 2 is the string name

See PreparedStatement.setString[^]

Posted 31-Jan-18 6:35am

Richard MacCutchan

Comments

four systems 1-Feb-18 3:20am

thanks, is it possible to add records from csv file to database cause there are lots of records

Richard MacCutchan 1-Feb-18 4:26am

Yes, just the same as adding any data to a database. You just need to write the code that reads each record and inserts it into the database.

four systems 1-Feb-18 5:23am

thanks

four systems · Answer 2 · 2018-02-01T02:52:00

Code for bulk insertion of values from file to database the error is
"Syntax error: Encountered "load" at line 1, column 1.

package Rdbms;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

public class jdbcaddfromfile {

	private static final String DB_DRIVER = "org.apache.derby.jdbc.ClientDriver";
	private static final String DB_CONNECTION = "jdbc:derby://localhost:1527/Oracle;create=true;";
	private static final String DB_USER = "Android";
	private static final String DB_PASSWORD = "java";

	public static void main(String[] argv) {

		try {

			insertRecordIntoTable();

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		}

	}

	private static void insertRecordIntoTable() throws SQLException {

		Connection dbConnection = null;
		PreparedStatement preparedStatement = null;

		String insertTableSQL = "load data infile 'c:/temp/some_data.txt' \n" +
                                        "replace \n" +
                                        "into table GOOD \n" +
                                        "columns terminated by '\\t' \n" +
                                        "ignore 1 lines";;

		try {
			dbConnection = getDBConnection();
			preparedStatement = dbConnection.prepareStatement(insertTableSQL);

			preparedStatement.executeUpdate();

			System.out.println("Record is inserted into GOOD table!");

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		} finally {

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

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

		}

	}

	private static Connection getDBConnection() {

		Connection dbConnection = null;

		try {

			Class.forName(DB_DRIVER);

		} catch (ClassNotFoundException e) {

			System.out.println(e.getMessage());

		}

		try {

			dbConnection = DriverManager.getConnection(
                            DB_CONNECTION, DB_USER,DB_PASSWORD);
			return dbConnection;

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		}

		return dbConnection;

	}

	private static java.sql.Timestamp getCurrentTimeStamp() {

		java.util.Date today = new java.util.Date();
		return new java.sql.Timestamp(today.getTime());

	}

}

package Rdbms;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

public class jdbcaddfromfile {

	private static final String DB_DRIVER = "org.apache.derby.jdbc.ClientDriver";
	private static final String DB_CONNECTION = "jdbc:derby://localhost:1527/Oracle;create=true;";
	private static final String DB_USER = "Android";
	private static final String DB_PASSWORD = "java";

	public static void main(String[] argv) {

		try {

			insertRecordIntoTable();

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		}

	}

	private static void insertRecordIntoTable() throws SQLException {

		Connection dbConnection = null;
		PreparedStatement preparedStatement = null;

		String insertTableSQL = "load data infile 'c:/temp/some_data.txt' \n" +
                                        "replace \n" +
                                        "into table GOOD \n" +
                                        "columns terminated by '\\t' \n" +
                                        "ignore 1 lines";;

		try {
			dbConnection = getDBConnection();
			preparedStatement = dbConnection.prepareStatement(insertTableSQL);

			preparedStatement.executeUpdate();

			System.out.println("Record is inserted into GOOD table!");

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		} finally {

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

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

		}

	}

	private static Connection getDBConnection() {

		Connection dbConnection = null;

		try {

			Class.forName(DB_DRIVER);

		} catch (ClassNotFoundException e) {

			System.out.println(e.getMessage());

		}

		try {

			dbConnection = DriverManager.getConnection(
                            DB_CONNECTION, DB_USER,DB_PASSWORD);
			return dbConnection;

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		}

		return dbConnection;

	}

	private static java.sql.Timestamp getCurrentTimeStamp() {

		java.util.Date today = new java.util.Date();
		return new java.sql.Timestamp(today.getTime());

	}

}

"

package Rdbms;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

public class jdbcaddfromfile {

	private static final String DB_DRIVER = "org.apache.derby.jdbc.ClientDriver";
	private static final String DB_CONNECTION = "jdbc:derby://localhost:1527/Oracle;create=true;";
	private static final String DB_USER = "Android";
	private static final String DB_PASSWORD = "java";

	public static void main(String[] argv) {

		try {

			insertRecordIntoTable();

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		}

	}

	private static void insertRecordIntoTable() throws SQLException {

		Connection dbConnection = null;
		PreparedStatement preparedStatement = null;

		String insertTableSQL = "load data infile 'c:/temp/some_data.txt' \n" +
                                        "replace \n" +
                                        "into table GOOD \n" +
                                        "columns terminated by '\\t' \n" +
                                        "ignore 1 lines";;

		try {
			dbConnection = getDBConnection();
			preparedStatement = dbConnection.prepareStatement(insertTableSQL);

			preparedStatement.executeUpdate();

			System.out.println("Record is inserted into GOOD table!");

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		} finally {

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

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

		}

	}

	private static Connection getDBConnection() {

		Connection dbConnection = null;

		try {

			Class.forName(DB_DRIVER);

		} catch (ClassNotFoundException e) {

			System.out.println(e.getMessage());

		}

		try {

			dbConnection = DriverManager.getConnection(
                            DB_CONNECTION, DB_USER,DB_PASSWORD);
			return dbConnection;

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		}

		return dbConnection;

	}

	private static java.sql.Timestamp getCurrentTimeStamp() {

		java.util.Date today = new java.util.Date();
		return new java.sql.Timestamp(today.getTime());

	}

}