从控制台到数据库java.sql.sqlexception的Java jdbc记录： [英] Java jdbc records from console to database java.sql.sqlexception:

问题描述

从用户获取并插入数据库但给出错误的值的代码



java.sql.SQLException：列位置'45'不在此ResultSet的列数为'2'。



当用户输入45作为数字时，代码将其作为列号，即2

必须更改的内容，以便number是列id中的文字，表中有两列名为ID和Name;

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

推荐答案

你是使用输入数字，而不是SetString语句中的列索引。它应该是这样的：

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

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

见 PreparedStatement.setString [ ^ ]

从文件到数据库批量插入值的代码错误是

语法错误：在第1行第1列遇到加载。

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.sql.sqlexception的Java jdbc记录：的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！