Mysql select query in java

In order to retrieve results you need to move the cursor to the next row, to do that you can use, If the cursor moved to the next row successfully (which means there are more results) it will return otherwise . Well, the first problem is that you’re opening yourself up to a SQL injection attack by including values directly in your SQL.

Mysql select query in java

I’m new to connecting java with a mysql database. What’s wrong with my query here:

PreparedStatement statement = conn.prepareStatement("SELECT * FROM q_table, choices, answers WHERE q_table.QID='" + number_input + "' AND choices.CID='" + number_input + "' AND answers.AID='" + number_input + "'");

In your statement » . q_table.QID='» + number_input + «‘ AND . the variable number_input is enclosed in a single quote (‘). This is used for string lieterals. If you remove the single quote it should work:

 String prest= "SELECT * FROM q_table, choices, answers WHERE questions.QID=? AND choices.CID=? AND answers.AID=?"; prest.setInt(1,1980); prest.setInt(2,2004); . . ResultSet rs = prest.executeQuery(); while (rs.next()) < String mov_name = rs.getString(1); int mov_year = rs.getInt(2); count++; System.out.println(mov_name + "\t" + "- " + mov_year); >System.out.println("Number of records: " + count); prest.close(); con.close();

Well, the first problem is that you’re opening yourself up to a SQL injection attack by including values directly in your SQL. Use a parameterized query instead.

Now we can’t really tell what’s wrong beyond that, although the fact that you’re quoting a number seems suspicious, as does the fact that you’re using the same value for a question ID, a choice ID and an answer ID. That seems unlikely to be appropriate.

If you could give us more information about what’s happening vs what you expected to happen, that would really help.

When you use prepared statements, you can’t set the values there.

You have to first prepare the statement using question marks and then set the parameters later.

public void updateCoffeeSales(HashMap salesForWeek) throws SQLException < PreparedStatement updateSales = null; PreparedStatement updateTotal = null; String updateString = "update " + dbName + ".COFFEES " + "set SALES = ? where COF_NAME = ?"; String updateStatement = "update " + dbName + ".COFFEES " + "set TOTAL = TOTAL + ? where COF_NAME = ?"; try < con.setAutoCommit(false); updateSales = con.prepareStatement(updateString); updateTotal = con.prepareStatement(updateStatement); for (Map.Entrye : salesForWeek.entrySet()) < updateSales.setInt(1, e.getValue().intValue()); updateSales.setString(2, e.getKey()); updateSales.executeUpdate(); updateTotal.setInt(1, e.getValue().intValue()); updateTotal.setString(2, e.getKey()); updateTotal.executeUpdate(); con.commit(); >> catch (SQLException e ) < JDBCTutorialUtilities.printSQLException(e); if (con != null) < try < System.err.print("Transaction is being rolled back"); con.rollback(); >catch(SQLException excep) < JDBCTutorialUtilities.printSQLException(excep); >> > finally < updateSales.close(); updateTotal.close(); con.setAutoCommit(true); >>

SELECT * FROM q_table , choices, answers WHERE q_table .QID='» + number_input + «‘ AND choices.CID='» + number_input + «‘ AND answers.AID='» + number_input + «‘»

SELECT * FROM questions , choices, answers WHERE questions .QID='» + number_input + «‘ AND choices.CID='» + number_input + «‘ AND answers.AID='» + number_input + «‘»

Query a MySQL db using java, getConnection(«jdbc:mysql://localhost:3306/DBNAME», «usrname», «pswd») ; Statement stmt = conn.createStatement() ; String query = «select

Java-MySQL Select

This is a fast tutorial, intended for people who understand java, and the basics of SQL/MySQL
Duration: 9:48

How to perform select query on a database of mysql using java

Retrieving Data Using the MySQL SELECT Statement

kkjavatutorials #mysqlAbout this Video:In this video, We will learn How to retrieve Data Using Duration: 3:14

Select query in MySql table [duplicate]

I have two tables table1 which has id(primary key)and table2 which has an name, id(foreign key). All ids are null initially in table2. Now whenever a new id is generated in parent table we have to select the very first row from table2 which has id null and update that row with the new id. Now select * from table2 where id is null fetches a set of rows but is there a way to modify this sql query so that it fetches me the very first row of table2 where id column is null.

select * from table2 where id is null limit 1

Limit specifies number of records you want to fetch.

SELECT * FROM table2 WHERE id is null LIMIT (n - 1), 1

example: SELECT * FROM table2 WHERE id is null LIMIT 2, 1

Select multiple columns from MySql to Java, executeQuery(query); while (rs.next()) < >st2.close(); > catch (SQLException e) < System.out.println(e); >return

How to use textfield input in mysql SELECT query

I am using Java netbeans and mysql. I want to check whether the value entered by the user in a textfield tf is already present in the mysql table or not.

 String query1="SELECT * FROM trytable WHERE name='8'"; ResultSet rs=stmt.executeQuery(query1); if(rs.isBeforeFirst()==true)

In the above code in place of 8 I want to give the value that the user input in the form and then check whether that value already exist in form or not.

Please help me in the first line . Thanks

You should use a PreparedStatement instead of a regular statement. This is more secure than a normal Statement and allows you to avoid SQL injection issues.

You would change your query like so:

String query = "SELECT * FROM trytable WHERE name='?';";

Note the ? at the end of the query. This can be replaced later in your code when setting up the PreparedStatement :

PreparedStatement preparedStatement = connection.prepareStatement(query); preparedStatement.setString(1, userInput); ResultSet rs = preparedStatement.executeQuery(); if (rs.next()) System.out.println("Record exists!");

Here, you are telling the prepared statement to replace the first ? in the query, with the value of userInput . So, if the user inputs a 3, the query that gets executed would be SELECT * FROM trytable WHERE name=3; .

Also note that rs.next() returns true if the query returns any results, so that would be the proper way to determine if the record exists.

ResultSet is like a table, it has a cursor. At the beginning the cursor is above the first row so isBeforeFirst() will always return true even there are no results in the ResultSet .

In order to retrieve results you need to move the cursor to the next row, to do that you can use,

If the cursor moved to the next row successfully (which means there are more results) it will return true otherwise false . As you only need the first result you can also use,

to confirm there are data available in the returned ResultSet .

This is the final code will is working absolutely fine.

try < Class.forName("com.mysql.jdbc.Driver"); Connection conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql","root",""); String query = "SELECT * FROM table_name WHERE name=?;"; PreparedStatement preparedStatement = conn.prepareStatement(query); preparedStatement.setString(1,jtf.getText()); ResultSet rs = preparedStatement.executeQuery(); if(rs.next()==true)< JOptionPane.showMessageDialog(null,"Value already exist"); >else < JOptionPane.showMessageDialog(null,"Value not present"); String query1="INSERT INTO table_name(col_name) VALUES (?)"; preparedStatement = conn.prepareStatement(query1); preparedStatement.setString(1,jtf.getText()); preparedStatement.execute(); JOptionPane.showMessageDialog(null,"DONE"); >rs.close(); preparedStatement.close(); > catch(Exception e)

Java & MySQL, ResultSet executeQuery (String SQL) − Returns a ResultSet object. Use this method when you expect to get a result set, as you would with a SELECT statement.

Where Clause in MySQL

I’m newbie with mysql and this is my issue. I have a table in database called Room with one of its attribute as Floor. In the Floor column, there are 3 different names ( Walters, Gates, Pension).

I’m trying to fetch figures from table based on the selected floor and this is my query.

 String query = "Select * from Rooms where Floor =" + Floor.getSelectedItem().toString();

This seems like the right query but i get an error thrown saying Unknown Column ‘Walters’ in where clause.

What am i not doing right?

Your query is not correct, because String should be between to quotes «» :

String query = "Select * from Rooms where Floor = '" + Floor.getSelectedItem().toString() + "'"; //----------------------------------^------------------------------------------^

But this not secure, instead read about Prepared Statement to avoid SQL Injection and syntax error :

String query = "Select * from Rooms where Floor = ?"; PreparedStatement pstmt = con.prepareStatement(query); pstmt.setString(1, Floor.getSelectedItem().toString()); pstmt.executeQuery();//then execute the statement

strings in a query should be in single quotes, your query should be:

Select * from Rooms where Floor = 'Walter';

String query = "Select * from Rooms where Floor = '" + Floor.getSelectedItem().toString() + "'";

It is better to use PreparedStatements to avoid confusion

String query = «Select * from Rooms where Floor ='» + Floor.getSelectedItem().toString();+»‘»

How to fetch only a single result from a table in Java-MySQL?, Let us first create a table − mysql> create table DemoTable -> ( -> Id int, -> Name varchar(20) -> ); Query OK, 0 rows affected (1.37 sec).

Источник

A Java MySQL SELECT example

Summary: This is a Java/MySQL SQL SELECT example, demonstrating how to issue a SQL SELECT command from your Java source code, using a MySQL database.

I’ve written a short example program that shows how to perform a SELECT query against a MySQL database in Java. Let’s take a look at it.

An example MySQL database table

The first thing we need for our SQL SELECT query example is a sample database table. To keep it simple — but also show several different MySQL data types — I’ve created the following example database table:

create table users ( id int unsigned auto_increment not null, first_name varchar(32) not null, last_name varchar(32) not null, date_created timestamp default now(), is_admin boolean, num_points int, primary key (id) );

A few of these MySQL fields are a little contrived, but I wanted to show several different data types in one table, and this is what I came up with. In particular, the field num_points is a little unusual. I made it up so I could show an int data type in this table, and I was thinking of those websites where points are awarded for giving correct answers.

I’ve populated this database table in some related articles (Java MySQL INSERT using Statement, Java MySQL INSERT using PreparedStatement), so when I run this SELECT query from the MySQL command prompt:

+----+------------+-----------+---------------------+----------+------------+ | id | first_name | last_name | date_created | is_admin | num_points | +----+------------+-----------+---------------------+----------+------------+ | 2 | Fred | Flinstone | 2010-06-23 00:00:00 | 0 | 6000 | | 3 | Barney | Rubble | 2010-06-23 00:00:00 | 0 | 5000 | +----+------------+-----------+---------------------+----------+------------+ 2 rows in set (0.00 sec)

With a little example data in my database, I’m ready to write some Java/JDBC code.

The Java source code

To perform a SQL SELECT query from Java, you just need to follow these steps:

Create a Java Connection to the MySQL database
Define the SELECT statement
Execute the SELECT query, getting a Java ResultSet from that query
Iterate over the ResultSet , getting the database fields (columns) from each row of data that is returned
Close the Java database connection
Catch any SQL exceptions that may come up during the process

I tried to document the following Java/MySQL SELECT example so you can see these steps. Note that in this example my MySQL database username is “root”, my password is blank, and the database is running on the same computer where this program is run, so the database host name is “localhost”:

import java.sql.*; /** * A Java MySQL SELECT statement example. * Demonstrates the use of a SQL SELECT statement against a * MySQL database, called from a Java program. * * Created by Alvin Alexander, http://alvinalexander.com */ public class JavaMysqlSelectExample < public static void main(String[] args) < try < // create our mysql database connection String myDriver = "org.gjt.mm.mysql.Driver"; String myUrl = "jdbc:mysql://localhost/test"; Class.forName(myDriver); Connection conn = DriverManager.getConnection(myUrl, "root", ""); // our SQL SELECT query. // if you only need a few columns, specify them by name instead of using "*" String query = "SELECT * FROM users"; // create the java statement Statement st = conn.createStatement(); // execute the query, and get a java resultset ResultSet rs = st.executeQuery(query); // iterate through the java resultset while (rs.next()) < int String firstName = rs.getString("first_name"); String lastName = rs.getString("last_name"); Date dateCreated = rs.getDate("date_created"); boolean isAdmin = rs.getBoolean("is_admin"); int numPoints = rs.getInt("num_points"); // print the results System.out.format("%s, %s, %s, %s, %s, %s\n", id, firstName, lastName, dateCreated, isAdmin, numPoints); >st.close(); > catch (Exception e) < System.err.println("Got an exception! "); System.err.println(e.getMessage()); >> >

The results

Assuming that everything is set up properly on your computer system, you should see output like this when you run this example Java program:

2, Fred, Flinstone, 2010-06-23, false, 6000 3, Barney, Rubble, 2010-06-23, false, 5000

Of course your output will vary depending on the actual data in your database table.

Regarding your setup, the main things you’ll need are a Java compiler (SDK), the MySQL JDBC database driver, and a MySQL instance running on your computer (or running on another computer you can access).

In “real world” Java database programs I almost always use the Spring JDBC library to access a database, but when you’re first getting started, I think it’s important to see examples like this so you can understand how things work under the covers.

Summary: My Java MySQL SELECT example

As a quick recap, this example demonstrated the following steps:

How to create a Java Connection to a MySQL database
How to create a SQL SELECT statement
How to execute a Java MySQL SELECT query, getting a Java ResultSet from that query
How to iterate over the Java ResultSet , getting the database fields (columns) from each row of data that is returned
How to close a Java database connection
How to catch any SQL exceptions that may come up during the process

In summary, I hope this Java MySQL SELECT example is helpful.

Источник