CODEWITHSUNDEEP
javamysqljdbc
Jakolcz
Jakolcz

Reputation: 564

MySQL SELECT doesn't work in JDBC

I have this MySQL query

SELECT 
    kompetence.kompetence_odlisujici_subcategories.nazev as odlisujici_nazev, 
    tolerovana, 
    zadana, 
    aktualni
FROM kompetence_odlisujici, kompetence.kompetence_odlisujici_subcategories
WHERE kompetence_odlisujici.os_cislo_zamestnanec = ? 
AND tolerovana <> 0 
AND zadana <> 0 
AND aktualni <> 0 
AND year = ? 
AND IPR.kompetence_odlisujici.active = 1
AND kompetence.kompetence_odlisujici_subcategories.id = PR.kompetence_odlisujici.id_odlisujici_subcategory

and it works fine if I try it in MySQL Workbench but when I try to use it in my java code I get this error

SQLException :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 'WHERE kompetence_odlisujici.os_cislo_zamestnanec = '123456' AND tolerovana <> 0 ' at line 1

What am I doing wrong?

Thanks for any help.

EDIT java code

DataSourcePool dspService = sling.getService(DataSourcePool.class);
DataSource dsMySql = (DataSource) dspService.getDataSource("myConnection");
PreparedStatement preparedStatement = null;
if(dsMySql != null){
String sqlQuery = "SELECT kompetence.kompetence_odlisujici_subcategories.nazev as odlisujici_nazev, tolerovana, zadana, aktualni"+
                "FROM IPR.kompetence_odlisujici, kompetence.kompetence_odlisujici_subcategories "+
                "WHERE IPR.kompetence_odlisujici.os_cislo_zamestnanec = ? AND tolerovana <> 0 AND zadana <> 0 AND aktualni <> 0 AND year = ? AND IPR.kompetence_odlisujici.active = 1"+
                "AND kompetence.kompetence_odlisujici_subcategories.id = IPR.kompetence_odlisujici.id_odlisujici_subcategory";
  Connection mySqlConnection = mySqlConnection = dsMySql.getConnection();
    preparedStatement = mySqlConnection.prepareStatement(sqlQuery);
  String workingUser = this.getWorkingUser();
  int year = Calendar.getInstance().get(Calendar.YEAR);
    preparedStatement.setString(1, workingUser);
    preparedStatement.setInt(2, year);
  ResultSet resultSet = preparedStatement.executeQuery();
  resultSet.last();
    int count = resultSet.getRow();
    resultSet.beforeFirst();
  /*
  parse result set here
  */
}

Upvotes: 0

Views: 267

Answers (1)

m0skit0
m0skit0

Reputation: 25873

String sqlQuery = "SELECT kompetence.kompetence_odlisujici_subcategories.nazev as odlisujici_nazev, tolerovana, zadana, aktualni "+
                "FROM IPR.kompetence_odlisujici, kompetence.kompetence_odlisujici_subcategories "+
                "WHERE IPR.kompetence_odlisujici.os_cislo_zamestnanec = ? AND tolerovana <> 0 AND zadana <> 0 AND aktualni <> 0 AND year = ? AND IPR.kompetence_odlisujici.active = 1 "+
                "AND kompetence.kompetence_odlisujici_subcategories.id = IPR.kompetence_odlisujici.id_odlisujici_subcategory";

No space between aktualni and FROM, and no space between IPR.kompetence_odlisujici.active = 1 and AND kompetence.kompetence_odlisujici_subcategories.id = IPR.kompetence_odlisujici.id_odlisujici_subcategory.

Also my suggestion is that you make these queries as private static final in the class.

Upvotes: 2

Related Questions