• We've upgraded our forums. Please post any issues/requests in this thread.

SQL Operations in Java

Joined
Jun 30, 2008
Messages
1,135 (0.33/day)
Likes
67
Location
England
System Name Wasleys PC
Processor Intel i5 2400 3.10GHz
Motherboard Asus P8z68-V
Cooling AC Freezer Pro
Memory Kingston 4GB (2x2GB) DDR3 Hyperx Memory
Video Card(s) HIS ATi 6850 1GB DDR3
Storage Seagate ST3500320AS 500GB Hard Drive SATA II 7200rpm *32MB Cache*
Case Antec 900 with mods
Audio Device(s) On Board
Power Supply OCZ Stealth Xtream 500W
Software Windows 7
#1
Hi all,

I'm trying to create an if statement that:
"When a user input equals ANY of the the data in column "userID", Then execute"

I'm aware you can use the .equals operation but not sure how you would link it to the column within the database.

Wozzer
 

Mindweaver

Moderato®™
Staff member
Joined
Apr 16, 2009
Messages
6,583 (2.08/day)
Likes
3,915
Location
Charleston, SC
System Name Sechs / EyeSeven
Processor i7 5820k @ 4.5ghz / i7 2600k @ 4.0Ghz
Motherboard X99S GAMING 7 / ASRock Z68 Extreme3 Gen3
Cooling CORSAIR Hydro Series H100i Extreme / Stock cooler... H50 leaked!..
Memory G.Skill DDR4 2800 16GB 4x4GB / G.Skill DDR3 1600 8gb 2x4GB
Video Card(s) PNY Quadro K2000 / MSI GTX 970 GAMING 4Gb
Storage Samsung 850 PRO 256GB SSD /Intel 240gb, corsair 120gb SSD, 3x 1 tb s storage
Display(s) 2x Acer G276HL 27" 1080p / 3x Acer 22" Nvidia surround
Case Phanteks “Enthoo Pro series”/ Cooler Master Storm Scout
Audio Device(s) Azalia Realtek ALC1150 / SB X-Fi Gamer
Power Supply Corsair CXM CX600M / Corsair 600w
Mouse Razer DeathAdder Chroma / Razer DeathAdder
Keyboard Razer BlackWidow Ultimate /
Software Windows 10 Pro x64 / Windows 10 Pro x64
#2
Have you loaded the JDBC driver, and successfully connected to your database?
 
Joined
Feb 26, 2008
Messages
4,870 (1.36/day)
Likes
681
Location
Joplin, Mo
System Name Ultrabeast GX2
Processor Intel Core 2 Duo E8500 @ 4.0GHZ 24/7
Motherboard Gigabit P35-DS3L
Cooling Rosewill RX24, Dual Slot Vid, Fan control
Memory 2x1gb 1066mhz@850MHZ DDR2
Video Card(s) 9800GX2 @ 690/1040
Storage 750/250/250/200 all WD 7200
Display(s) 24" DCLCD 2ms 1200p
Case Apevia
Audio Device(s) 7.1 Digital on-board, 5.1 digital hooked up
Power Supply 700W RAIDMAXXX SLI
Software winXP Pro
Benchmark Scores 17749 3DM06
#3
Have you loaded the JDBC driver, and successfully connected to your database?
It seems like something he would have already done if he is at this point.

If you haven't, this would be the place to start.
 

Mindweaver

Moderato®™
Staff member
Joined
Apr 16, 2009
Messages
6,583 (2.08/day)
Likes
3,915
Location
Charleston, SC
System Name Sechs / EyeSeven
Processor i7 5820k @ 4.5ghz / i7 2600k @ 4.0Ghz
Motherboard X99S GAMING 7 / ASRock Z68 Extreme3 Gen3
Cooling CORSAIR Hydro Series H100i Extreme / Stock cooler... H50 leaked!..
Memory G.Skill DDR4 2800 16GB 4x4GB / G.Skill DDR3 1600 8gb 2x4GB
Video Card(s) PNY Quadro K2000 / MSI GTX 970 GAMING 4Gb
Storage Samsung 850 PRO 256GB SSD /Intel 240gb, corsair 120gb SSD, 3x 1 tb s storage
Display(s) 2x Acer G276HL 27" 1080p / 3x Acer 22" Nvidia surround
Case Phanteks “Enthoo Pro series”/ Cooler Master Storm Scout
Audio Device(s) Azalia Realtek ALC1150 / SB X-Fi Gamer
Power Supply Corsair CXM CX600M / Corsair 600w
Mouse Razer DeathAdder Chroma / Razer DeathAdder
Keyboard Razer BlackWidow Ultimate /
Software Windows 10 Pro x64 / Windows 10 Pro x64
#4
It seems like something he would have already done if he is at this point.

If you haven't, this would be the place to start.
It would seem, but I'd like to know for sure. ;)

If it's just the Select statement then something like this:

Code:
"[color=blue]SELECT[/color] * [color=blue]FROM[/color] UserTable [color=blue]WHERE[/color] userID = [color=red]'[/color]" + UserInput + "[color=red]'[/color];"
 
Last edited:

Aquinus

Resident Wat-man
Joined
Jan 28, 2012
Messages
10,401 (4.84/day)
Likes
5,481
Location
Concord, NH
System Name Kratos
Processor Intel Core i7 3930k @ 4.2Ghz
Motherboard ASUS P9X79 Deluxe
Cooling Zalman CPNS9900MAX 130mm
Memory G.Skill DDR3-2133, 16gb (4x4gb) @ 9-11-10-28-108-1T 1.65v
Video Card(s) MSI AMD Radeon R9 390 GAMING 8GB @ PCI-E 3.0
Storage 2x120Gb SATA3 Corsair Force GT Raid-0, 4x1Tb RAID-5, 1x500GB
Display(s) 1x LG 27UD69P (4k), 2x Dell S2340M (1080p)
Case Antec 1200
Audio Device(s) Onboard Realtek® ALC898 8-Channel High Definition Audio
Power Supply Seasonic 1000-watt 80 PLUS Platinum
Mouse Logitech G602
Keyboard Rosewill RK-9100
Software Ubuntu 17.10
Benchmark Scores Benchmarks aren't everything.
#5
Instead of telling how how you're trying to solve the problem, maybe you should describe what you're doing (for the project,) and where you're at so we can have an idea about what you're trying to do rather than how you're trying to approach it.
 
Joined
Jan 2, 2012
Messages
1,016 (0.47/day)
Likes
1,420
Location
Indonesia
Processor AMD Ryzen R7 1700X @ 3.9 Ghz
Motherboard BIOSTAR X370GT7
Cooling Cryorig H5 Universal dual fan mode
Memory Team T-Force Night Hawk DDR4-3200 2x16 GB
Video Card(s) Inno3D GTX 1080 GAMING OC
Storage 512 GB Samsung 850 Pro + 3 x 2 TB WD Blue
Display(s) LG 27MB85Z
Case Fractal Design Define R5 Black
Power Supply Seasonic Prime 750W
Mouse Logitech G502
Keyboard TX-CP (Ergo Clear 68g) | KBParadise V80 (Matias Quiet Click)
Software Windows 10 version 1607 x64 + Ubuntu 16.04
#6
Hi all,

I'm trying to create an if statement that:
"When a user input equals ANY of the the data in column "userID", Then execute"

I'm aware you can use the .equals operation but not sure how you would link it to the column within the database.

Wozzer
I only understand a little bit Java, so pardon me If I'm wrong, userInput is the user input (string variable), users is your table name, and I assume you already have your connection to your database :

PHP:
SQLConnection.setURL("insert_connection_string_here");
con=SQLConnection.getNewConnection();
Statement stmt = con.createStatement();

// execute your SQL statement here, for example :
String sqlCommand = "SELECT userID FROM users WHERE userID='"+userInput+"'";  
ResultSet rs = stmt.executeQuery(sqlCommand);

bool hasRows = false;
while(rs.next()){
  hasRows = true;
  // put the codes that you want to execute here
}

if(!hasRows)
{
  // do stuff when no rows/match present.
}
Please CMIIW.

And

Instead of telling how how you're trying to solve the problem, maybe you should describe what you're doing (for the project,) and where you're at so we can have an idea about what you're trying to do rather than how you're trying to approach it.
+1.
 
Joined
Jun 30, 2008
Messages
1,135 (0.33/day)
Likes
67
Location
England
System Name Wasleys PC
Processor Intel i5 2400 3.10GHz
Motherboard Asus P8z68-V
Cooling AC Freezer Pro
Memory Kingston 4GB (2x2GB) DDR3 Hyperx Memory
Video Card(s) HIS ATi 6850 1GB DDR3
Storage Seagate ST3500320AS 500GB Hard Drive SATA II 7200rpm *32MB Cache*
Case Antec 900 with mods
Audio Device(s) On Board
Power Supply OCZ Stealth Xtream 500W
Software Windows 7
#7
Thanks for the responses. Wasn't expecting so many to reply :laugh:

To clarify - I have a java program. I only want users to access the software if they are registered.

I've set up a database (and connected to it!). I want to run a query which checks if the users ID and passwords ID match. If matched, permissions will be granted and the main menu will open.

Code:
 System.out.println("MySQL Connect Example.");
        Connection conn = null;
        String url = "jdbc:mysql://localhost:3306/";
        String dbName = "watson_users";
        String driver = "com.mysql.jdbc.Driver";
        String userName = "root";
        String password = "";
        try {
            Class.forName(driver).newInstance();
            conn = DriverManager.getConnection(url + dbName, userName, password);
            System.out.println("Connected to the database");

            Statement st = (Statement) conn.createStatement();

            String query_to_update = "SELECT * FROM `user_database` WHERE CollarID='" + collarIDField.getText() + "' AND Password='" + passwordField.getText() + "'";

            System.out.println("Query: " + query_to_update);

            int val = st.executeUpdate(query_to_update);
            System.out.println("We've got to this point");

            conn.close();
        } catch (InstantiationException | IllegalAccessException | SQLException ex) {
            Logger.getLogger(MetaDataExtractor.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(this, "Can't connect to Database");
        } catch (ClassNotFoundException ex) {
            JOptionPane.showMessageDialog(this, "Can't connect to Database");
        }
Error:
Code:
java.sql.SQLException: Can not issue SELECT via executeUpdate()
Edit: Just noticed .executeUpdate only allows for INSERT, DELETE, and UPDATE.
 
Last edited:
Joined
Aug 10, 2007
Messages
4,059 (1.07/day)
Likes
1,123
Location
Geneva, FL, USA
Processor Intel i5-6600
Motherboard ASRock H170M-ITX
Cooling Cooler Master Geminii S524
Memory G.Skill DDR4-2133 16GB (8GB x 2)
Video Card(s) Gigabyte R9-380X 4GB
Storage Samsung 950 EVO 250GB (mSATA)
Display(s) LG 29UM69G-B 2560x1080 IPS
Case Lian Li PC-Q25
Audio Device(s) Realtek ALC892
Power Supply Seasonic SS-460FL2
Mouse Logitech G700s
Keyboard Logitech G110
Software Windows 10 Pro
#8
Important is that you clean those input strings.

Code:
PreparedStatement selectUser = conn.prepareStatement("SELECT * FROM user_database WHERE CollarID= ? AND Password = ?");

selectUser.setString(1, collarIDField.getText());
selectUser.setString(2, passwordField.getText());

if ( selectUser.execute() ) {
	//get the data, welcome the user
} else {
	//tell them to get it right or bugger off
}
Now when I claim that my ID is "1; DROP TABLE user_database;" I'm not going to ruin your day. :)


(hope I got it right, only write JAVA when I have to).
 
Joined
Jan 2, 2012
Messages
1,016 (0.47/day)
Likes
1,420
Location
Indonesia
Processor AMD Ryzen R7 1700X @ 3.9 Ghz
Motherboard BIOSTAR X370GT7
Cooling Cryorig H5 Universal dual fan mode
Memory Team T-Force Night Hawk DDR4-3200 2x16 GB
Video Card(s) Inno3D GTX 1080 GAMING OC
Storage 512 GB Samsung 850 Pro + 3 x 2 TB WD Blue
Display(s) LG 27MB85Z
Case Fractal Design Define R5 Black
Power Supply Seasonic Prime 750W
Mouse Logitech G502
Keyboard TX-CP (Ergo Clear 68g) | KBParadise V80 (Matias Quiet Click)
Software Windows 10 version 1607 x64 + Ubuntu 16.04
#9
Edit: Just noticed .executeUpdate only allows for INSERT, DELETE, and UPDATE.
.executeUpdate() generally used for altering the databases. DROP TABLE or DATABASE, INSERT into TABLE, UPDATE TABLE, DELETE from TABLE statements will be used in this.

Use .executeQuery() or .execute() if you want to use SELECT. Remember the output is in the form of ResultSet.
 
Joined
Jun 30, 2008
Messages
1,135 (0.33/day)
Likes
67
Location
England
System Name Wasleys PC
Processor Intel i5 2400 3.10GHz
Motherboard Asus P8z68-V
Cooling AC Freezer Pro
Memory Kingston 4GB (2x2GB) DDR3 Hyperx Memory
Video Card(s) HIS ATi 6850 1GB DDR3
Storage Seagate ST3500320AS 500GB Hard Drive SATA II 7200rpm *32MB Cache*
Case Antec 900 with mods
Audio Device(s) On Board
Power Supply OCZ Stealth Xtream 500W
Software Windows 7
#10
Important is that you clean those input strings.

Code:
PreparedStatement selectUser = conn.prepareStatement("SELECT * FROM user_database WHERE CollarID= ? AND Password = ?");

selectUser.setString(1, collarIDField.getText());
selectUser.setString(2, passwordField.getText());

if ( selectUser.execute() ) {
	//get the data, welcome the user
} else {
	//tell them to get it right or bugger off
}
Now when I claim that my ID is "1; DROP TABLE user_database;" I'm not going to ruin your day. :)


(hope I got it right, only write JAVA when I have to).
Just to clarify - Is that to prevent SQL injection attacks, etc?

Thanks for all the replies. Working on it now.

Edit - Login seems to be working although I don't think it's checking the actual results as it's opening my program based on .execute
(IE - If anything is executed, do this).

How can I check the returned results from my database?
Thanks all!
:toast:
 
Last edited:
Joined
Aug 10, 2007
Messages
4,059 (1.07/day)
Likes
1,123
Location
Geneva, FL, USA
Processor Intel i5-6600
Motherboard ASRock H170M-ITX
Cooling Cooler Master Geminii S524
Memory G.Skill DDR4-2133 16GB (8GB x 2)
Video Card(s) Gigabyte R9-380X 4GB
Storage Samsung 950 EVO 250GB (mSATA)
Display(s) LG 29UM69G-B 2560x1080 IPS
Case Lian Li PC-Q25
Audio Device(s) Realtek ALC892
Power Supply Seasonic SS-460FL2
Mouse Logitech G700s
Keyboard Logitech G110
Software Windows 10 Pro
#11
Last edited:
Joined
Jun 30, 2008
Messages
1,135 (0.33/day)
Likes
67
Location
England
System Name Wasleys PC
Processor Intel i5 2400 3.10GHz
Motherboard Asus P8z68-V
Cooling AC Freezer Pro
Memory Kingston 4GB (2x2GB) DDR3 Hyperx Memory
Video Card(s) HIS ATi 6850 1GB DDR3
Storage Seagate ST3500320AS 500GB Hard Drive SATA II 7200rpm *32MB Cache*
Case Antec 900 with mods
Audio Device(s) On Board
Power Supply OCZ Stealth Xtream 500W
Software Windows 7
#12
Yup. http://xkcd.com/327/

Even if the likelihood of revealing information is near impossible, it's still a best practice. No matter what they put in it's either going to work else not - with the "not" being handled the same way every time. Otherwise, one may be able to cause an error that reveals something of interest.


-Edit-

excute(), getResultSet():

http://docs.oracle.com/javase/1.4.2/docs/api/java/sql/PreparedStatement.html#execute()
Will read up on the documentation later tonight. Many thanks Jizzler.

lol @ the cartoon strip. :laugh: