尝试将java连接到mysql时获取MySQLSyntaxErrorException[duplicate]

Question

我不擅长java，还在尝试如何通过jdbc驱动程序将java连接到mysql（xampp），有人知道我为什么总是出错吗？

    public class connect {



        public static void main(String[] args) throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException{


            ResultSet rs = null;
            PreparedStatement pst = null;
            String hostName = "localhost";
            String dbPort = "3306";
            String databaseName = "Libraryusers";
            String dbUser = "root";
            String dbPassword = "";
            Class.forName("com.mysql.jdbc.Driver").newInstance();
          Connection conn = DriverManager.getConnection("jdbc:mysql://"+hostName+":"
                + dbPort+"/"+databaseName+"?"+"user="+dbUser+"&password=" + dbPassword);
            System.out.print("Database is connected !");

             String sql = "select * from login where username = ? and password = ?";

            pst = conn.prepareStatement(sql);
            pst.setString(1,"rod1");
            pst.setString(2,"rod");
            rs = pst.executeQuery(sql);
            if (rs.next())
            {
                System.out.println("username and password is correct");
            }
            else
            {
                System.out.println("invalid username and password");
            }





        }

}

以下是控制台结果

run:
Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? and password = ?' 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:2483)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2441)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1381)
at login.connect.main(connect.java:44)
Database is connected 
!C:\Users\rodchris\AppData\Local\NetBeans\Cache\8.2\executor-
snippets\run.xml:53: Java returned: 1
BUILD FAILED (total time: 0 seconds)

我已经创建了数据库并插入了两个数据，其中一个是“username=rod1，password=rod”

Answer 1

根据https://dev.mysql.com/doc/refman/5.7/en/keywords.html，密码是MySQLSQL中的保留字。因此，如果您想将其用作SQL标识符，则需要使用反引号对其进行引用；例如。

  String sql = "select * from login where username = ? and `password` = ?";

（但是用户名和登录不要……）

Answer 2

通过在executeQuery（SQL）再次指定SQL脚本，您取消了参数。。。

试试这样：

public static void main(String[] args)
      throws ClassNotFoundException, InstantiationException, IllegalAccessException,
             SQLException
{
   final String hostName = "localhost";
   final String dbPort = "3306";
   final String databaseName = "Libraryusers";
   final String dbUser = "root";
   final String dbPassword = "";

   // this is not needed anymore, but many still use it...
   Class.forName("com.mysql.jdbc.Driver").newInstance();

   try (final Connection conn = DriverManager.getConnection("jdbc:mysql://" + hostName + ":" +
                           dbPort + "/" + databaseName + "?user=" + dbUser + "&password=" +
                           dbPassword))
   {
      System.out.print("Database is connected !");

      final String sql = "SELECT * FROM `login` WHERE `username` = ? AND `password` = ?";

      try ( final PreparedStatement pst = conn.prepareStatement(sql))
      {
         pst.setString(1,"rod1");
         pst.setString(2,"rod");
         try (final ResultSet rs = pst.executeQuery())
         {
            if (rs.next())
            {
               System.out.println("username and password is correct");
            }
            else
            {
               System.out.println("invalid username and password");
            }
         }
      }
   }
}