SQL Insert, Select, Update, and Delete Examples. Details Last Updated on 1. January 2. 01. 6 . These CRUD operations are equivalent to the INSERT, SELECT, UPDATE and DELETE statements in SQL language. Although the target database system is My. SQL, but the same technique can be applied for other database systems as well because the query syntax used is standard SQL which is supported by all relational database systems. A JDBC tutorial for executing basic SQL statements like INSERT, SELECT,UPDATE and DELETE. Andy Bailey ".The PrimeFaces family of components has proved invaluable in greatly reducing time to market for both new applications and the. We will learn how to do insert, query, update and delete database records by writing code to manage records of a table Users in a My. SQL database called Sample. DB. Table of content: Prerequisites. Creating a sample My. SQL database. The principal JDBC interfaces and classes. Connecting to the database. Executing INSERT statement.
Executing SELECT statement. Executing UPDATE statement. Executing DELETE statement. Prerequisites To begin, make sure you have the following pieces of software installed on your computer: 2. Creating a sample My. Code Library thread about Single RadioButton check at a time with row selection in UI for ASP.NET AJAX. Join the conversation now. Display Data from Database in JSF Application This Example demonstrates you how to display data from database in JSF application. SQL database Let’s create a My. SQL database called Sample. DB with one table Users with the following structure: Execute the following SQL script inside My. SQL Workbench: create database Sample. DB. CREATE TABLE `users` (. The principal JDBC interfaces and classes Let’s take an overview look at the JDBC’s main interfaces and classes with which we usually work. They are all available under the java. Driver. Manager: this class is used to register driver for a specific database type (e. My. SQL in this tutorial) and to establish a database connection with the server via its get. Connection() method. Connection: this interface represents an established database connection (session) from which we can create statements to execute queries and retrieve results, get metadata about the database, close connection, etc. Statement and Prepared. Statement: these interfaces are used to execute static SQL query and parameterized SQL query, respectively. Statement is the super interface of the Prepared. Statement interface. JSF Display DataTable - Learn Java Server Faces (JSF) in simple and easy steps starting from Overview, Environment setup, Architecture, Life Cycle, First Application. Load data for the table's content from an Ajax source. Description. DataTables can obtain the data that it is to display in the table body from a number of sources. Their commonly used methods are: boolean execute(String sql): executes a general SQL statement. It returns true if the query returns a Result. Set, false if the query returns an update count or returns nothing. This method can be used with a Statement only. Update(String sql): executes an INSERT, UPDATE or DELETE statement and returns an update account indicating number of rows affected (e. Result. Set execute. Query(String sql): executes a SELECT statement and returns a Result. Set object which contains results returned by the query. A prepared statement is one that contains placeholders (in form question marks ?) for dynamic values will be set at runtime. For example: SELECT * from Users WHERE user. Use this object to iterate over rows in the result set using next() method, and get value of a column in the current row using get. XXX() methods (e. The column value can be retrieved either by index number (1- based) or by column name. SQLException: this checked exception is declared to be thrown by all the above methods, so we have to catch this exception explicitly when calling the above classes’ methods. Connecting to the database Supposing the My. SQL database server is listening on the default port 3. The following code snippet connects to the database name Sample. DB by the user root and password secret: String db. URL = . In the above code, we have to close the connection explicitly after finish working with the database: conn. However, since Java 7, we can take advantage of the try- with- resources statement which will close the connection automatically, as shown in the following code snippet: try (Connection conn = Driver. Manager. get. Connection(db. URL, username, password)) . The sample programs in this tutorial are all using this try- with- resources statement to make a database connection. NOTE: For details about connecting to a My. SQL database, see the article: Connect to My. SQL database via JDBC. Executing INSERT statement Let’s write code to insert a new record into the table Users with following details: username: billpassword: secretpassfullname: Bill Gatesemail: bill. Here’s the code snippet: String sql = . To set values for the parameters in the INSERT statement, we use the Prepared. Statement. Note that the parameter index is 1- based (unlike 0- based index in Java array). The Prepared. Statement interface provides various set. XXX() methods corresponding to each data type, for example: set. Boolean(int parameter. Index, boolean x)set. Date(int parameter. Index, Date x)set. Float(int parameter. Index, float x). Which method to be used is depending on the type of the corresponding column in the database table. Finally we call the Prepared. Statement’s execute. Update() method to execute the INSERT statement. This method returns an update count indicating how many rows in the table were affected by the query, so checking this return value is necessary to ensure the query was executed successfully. In this case, execute. Update() method should return 1 to indicate one record was inserted. Executing SELECT statement The following code snippet queries all records from the Users table and print out details for each record: String sql = . The while loop iterates over the rows contained in the result set by repeatedly checking return value of the Result. Set’s next() method. The next() method moves a cursor forward in the result set to check if there is any remaining record. For each iteration, the result set contains data for the current row, and we use the Result. Set’s get. XXX(column index/column name) method to retrieve value of a specific column in the current row, for example this statement: String name = result. String(2). Retrieves value of the second column in the current row, which is the username field. The value is casted to a String because we know that the username field is of type VARCHAR based on the database schema mentioned previously. Keep in mind that the column index here is 1- based, the first column will be at index 1, the second at index 2, and so on. If you are not sure or don’t know exactly the index of column, so passing a column name would be useful: String fullname = result. String(. Executing UPDATE statement The following code snippet will update the record of “Bill Gates” as we inserted previously: String sql = ? WHERE username=? Executing DELETE statement The following code snippet will delete a record whose username field contains “bill”: String sql = . The key points to remember are: Using a Statement for a static SQL query. Using a Prepared. Statement for a parameterized SQL query and using set. XXX() methods to set values for the parameters. Using execute() method to execute general query. Using execute. Update() method to execute INSERT, UPDATE or DELETE query. Using execute. Query() method to execute SELECT query. Using a Result. Set to iterate over rows returned from a SELECT query, using its next() method to advance to next row in the result set, and using get. XXX() methods to retrieve values of columns. You can download source code of sample demo programs for each type of query in the attachments section. Quick Tip: Click Table Row to Trigger a Checkbox Click. Somebody on the j. Query Google Group asked the other day about toggling a checkbox within a table row when the user clicked anywhere within the row. This can be a nice feature to have, and it's also very straightforward to implement. The Basics. The one tricky part is that we don't want to trigger the click if the user clicks on the checkbox itself, because then it would effectively be clicked twice, once by the user and once programmatically. To avoid this, we can use the event argument inside the click handler. Let's take a look at how the code might be written: Java. Script: $(document). This way, the user will have one more visual indicator that the checkbox has been checked. We'll use the . toggle. Class() method so that it adds the class if it isn't there, and removes it if it is: Notice that the . Class() line is outside of the if statement. We want the class to toggle whether the actual target of the click is on the checkbox or not. If you look carefully at this table, you'll notice that the second row has a little problem. Since its checkbox is initially checked, the first click on that row adds the . Then we carry on with binding the click handler to the rows. This example should work as expected: These rows are also enhanced by a little CSS rule: . This gives the entire row the same cursor as the checkbox. The rule is applied only if Java. Script is enabled (see my previous post for details). Update. As of j. Query 1. Class() part of this code doesn't appear to work. In fact, it is being fired twice on each click. The problem has arisen because event bubbling is now supported for the . As the documentation states, . For example if you trigger an event on a ? I have two solutions, and I welcome others. Bind to the Checkbox. The first solution involves binding a click handler directly to the checkboxes for the . Class(). In this way, we take advantage of the triggered event bubbling. This fixes the problem, but I'm not crazy about it. It doubles the number of elements that have events bound to them, and I've been trying lately to reduce bound elements. Change the checked Attribute The second solution forgoes the click trigger in favor of simply changing the value of the checkbox's checked attribute. Java. Script: $(document). Again, if you have a better idea, please let me know (by posting it in the comments). With the updates, this hardly qualifies as a quick tip anymore. Oh well—back to our regularly scheduled blog post. If we try to achieve this effect with links, though, we'll have to do it a little differently. Here is a quick example: Java. Script: $(document).
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
August 2017
Categories |