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

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());

	}

}

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

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

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

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us