Java JDBC MySQL Connection searchForItemId(String item)

Description

search For Item Id

License

Open Source License

Declaration

public static int searchForItemId(String item) 

Method Source Code

//package com.java2s;
//License from project: Open Source License 

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Main {
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://127.0.0.1:3306/twitch";
    static final String USER = "java";
    static final String PASS = "java";

    public static int searchForItemId(String item) {

        Connection dbConnection = null;

        PreparedStatement preparedStatement = null;

        String sql = "SELECT itemId as id FROM items WHERE itemName = ?";

        try {/*from   ww  w . j  ava2  s  .  c  o m*/
            dbConnection = getDBConnection();
            preparedStatement = dbConnection.prepareStatement(sql);

            preparedStatement.setString(1, item);

            logPreparedStatement(preparedStatement);

            ResultSet rs = preparedStatement.executeQuery();

            if (rs.next()) {
                int id = rs.getInt("id");
                return id;
            } else {
                System.err.println("no next");

            }

        } catch (SQLException e) {

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

        }

        return -2;

    }

    private static Connection getDBConnection() {

        Connection dbConnection = null;

        try {

            Class.forName(JDBC_DRIVER);

        } catch (ClassNotFoundException e) {

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

        }

        try {

            dbConnection = DriverManager.getConnection(DB_URL, USER, PASS);
            return dbConnection;

        } catch (SQLException e) {

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

        }

        return dbConnection;

    }

    public static void logPreparedStatement(PreparedStatement p) {
        System.out.println("[QUERY] : " + p.toString().split(": ")[1]);
    }
}

Related

1. putData(int tID, String toolID)
2. query(String sql)
3. queryAll(String sql, List