This is a list of handy MySQL commands that I use time and time again. At the bottom are statements, clauses, and functions you can use in MySQL. Spring 4 MVCHibernate 4My. SQLMaven integration example using annotations. In this tutorial, we will integrate Spring with Hibernate using annotation based configuration. We will develop a simple CRUD oriented web application containing a form asking user input, saving that input in My. SQL database using Hibernate, retrieving the records from database and updating or deleting them within transaction, all using annotation configuration. Testing part of this post is covered in detail in Next Post where we perform unitintegration test using Test. NG, mockito, spring test, DBUnit H2 database. To know more about testing with Test. NG in general, please refer our Test. NG Tutorials. Lets get going. Following technologies being used Spring 4. RELEASEHibernate Core 4. Finalvalidation api 1. Mysql C Api Update Example Sql InsertFinalhibernate validator 5. Final. My. SQL Server 5. Maven 3. JDK 1. 7. Tomcat 8. 0. 2. 1Eclipse JUNO Service Release 2. Test. NG 6. 9. 4. Mockito 1. 1. 0. 1. DBUnit 2. 2. H2 Database 1. Lets begin. Step 1 Create the directory structure. Following will be the final project structure Lets now add the content mentioned in above structure explaining each in detail. Step 2 Update pom. Locationhttp maven. POM4. 0. 0 http maven. POM4. 0. 0 xmlns xsihttp www. XMLSchema instance. Version 4. 0. Version. lt group. Id com. websystique. Id. lt artifact. Id Spring. Hibernate. Examplelt artifact. Id. lt packaging warlt packaging. Spring. Hibernate. Examplelt name. RELEASElt springframework. Finallt hibernate. Spring. lt dependency. Id org. springframeworklt group. Id. lt artifact. Id spring corelt artifact. Id. lt version springframework. Id org. springframeworklt group. Id. lt artifact. Id spring weblt artifact. Id. lt version springframework. Id org. springframeworklt group. Id. lt artifact. Id spring webmvclt artifact. Id. lt version springframework. Id org. springframeworklt group. Id. lt artifact. Id spring txlt artifact. Id. lt version springframework. Id org. springframeworklt group. Id. lt artifact. Id spring ormlt artifact. Id. lt version springframework. Hibernate. Id org. hibernatelt group. Id. lt artifact. Id hibernate corelt artifact. Id. lt version hibernate. Id javax. validationlt group. Id. lt artifact. Id validation apilt artifact. Id. lt version 1. Finallt version. Id org. hibernatelt group. Id. lt artifact. Id hibernate validatorlt artifact. Id. lt version 5. Finallt version. My. SQL. lt dependency. Id mysqllt group. Id. lt artifact. Id mysql connector javalt artifact. Id. lt version mysql. Joda Time. Id joda timelt group. Id. lt artifact. Id joda timelt artifact. Id. lt version joda time. To map Joda. Time with database type. Id org. jadira. Id. lt artifact. Id usertype. corelt artifact. Id. lt version 3. CR1lt version. ServletJSPJSTL. Id javax. servletlt group. Id. lt artifact. Id javax. servlet apilt artifact. Id. lt version 3. Id javax. servlet. Id. lt artifact. Id javax. servlet. Id. lt version 2. Id javax. servletlt group. Id. lt artifact. Id jstllt artifact. Id. lt version 1. Testing dependencies. Id org. springframeworklt group. Id. lt artifact. Id spring testlt artifact. Id. lt version springframework. Id org. testnglt group. Id. lt artifact. Id testnglt artifact. Id. lt version testng. Id org. mockitolt group. Id. lt artifact. Id mockito alllt artifact. Id. lt version mockito. Id com. h. 2databaselt group. Id. lt artifact. Id h. 2lt artifact. Id. lt version h. Id dbunitlt group. Id. lt artifact. Id dbunitlt artifact. Id. lt version dbunit. Management. lt plugins. Id org. apache. Id. lt artifact. Id maven compiler pluginlt artifact. Id. lt version 3. Id org. apache. Id. lt artifact. Id maven war pluginlt artifact. Id. lt version 2. Source. Directory srcmainwebapplt war. Source. Directory. Name Spring. Hibernate. Examplelt war. Name. lt fail. On. Missing. Web. Xml falselt fail. On. Missing. Web. Xml. lt configuration. Management. lt final. Name Spring. Hibernate. Examplelt final. Name. lt build. First thing to notice here is the maven war plugin declaration. As we are using full annotation configuration, we dont even include web. Since in this example we will use a form to accept input from user, we need also to validate the user input. We will choose JSR3. Validation here, so we have included validation api which represents the specification, and hibernate validator which represents an implementation of this specification. Email, Not. Empty, etc. which are not part of the specification. Along with that, we have also included JSPServletJstl dependencies which we will be needing as we are going to use servlet apis and jstl view in our code. In general, containers might already contains these libraries, so we can set the scope as provided for them in pom. We also have added testing dependencies. Testing part of this post is described in detail in Next post. Rest of the dependencies are for Spring, Hibernate and Joda Time. Step 3 Configure Hibernatecom. Hibernate. Configuration. Properties. import javax. Data. Source. import org. Session. Factory. Autowired. import org. Bean. import org. Component. Scan. import org. Configuration. import org. Property. Source. Environment. import org. Driver. Manager. Data. Source. import org. Hibernate. Transaction. Manager. import org. Local. Session. Factory. Bean. import org. Enable. Transaction. Management. Enable. Transaction. Management. Component. Scan com. Property. Sourcevalue classpath application. Hibernate. Configuration. Environment environment. Local. Session. Factory. Bean session. Factory. Local. Session. Factory. Bean session. Factory new Local. Session. Factory. Bean. session. Factory. Data. Sourcedata. Source. session. Factory. set. Packages. To. Scannew String com. Factory. set. Hibernate. Propertieshibernate. Properties. return session. Factory. public Data. Source data. Source. Driver. Manager. Data. Source data. Source new Driver. Manager. Data. Source. Source. set. Driver. Class. Nameenvironment. Required. Propertyjdbc. Class. Name. data. Source. set. Urlenvironment. Required. Propertyjdbc. Source. set. Usernameenvironment. Required. Propertyjdbc. Source. set. Passwordenvironment. Required. Propertyjdbc. Source. private Properties hibernate. Properties. Properties properties new Properties. Required. Propertyhibernate. Required. Propertyhibernate. Required. Propertyhibernate. Hibernate. Transaction. Manager transaction. ManagerSession. Factory s. Hibernate. Transaction. Manager tx. Manager new Hibernate. Transaction. Manager. Manager. set. Session. Factorys. return tx. Manager. Configuration indicates that this class contains one or more bean methods annotated with Bean producing beans manageable by spring container. In our case, this class represent hibernate configuration. Component. Scan is equivalent to context component scan base package. in xml, providing with where to look for spring managed beansclasses. Enable. Transaction. Management is equivalent to Springs tx XML namespace, enabling Springs annotation driven transaction management capability. Property. Source is used to declare a set of propertiesdefined in a properties file in application classpath in Spring run time Environment, providing flexibility to have different values in different application environments. Method session. Factory is creating a Local. Session. Factory. Bean, which exactly mirrors the XML based configuration We need a data. Source and hibernate properties same as hibernate. Thanks to Property. Source, we can externalize the real values in a. Springs Environment to fetch the value corresponding to an item. Once the Session. Factory is created, it will be injected into Bean method transaction. My. SQL Commands. This is a list of handy My. SQL commands that I use time and time again. At the bottom are statements, clauses. My. SQL. Below that are PHP and Perl API functions you can use to interface with My. SQL. To use. those you will need to build PHP with My. SQL functionality. To use My. SQL with Perl you will need to use the Perl modules. DBI and DBD mysql. Below when you see it means from the unix shell. When you see mysql it means from a My. SQL prompt after logging. My. SQL. To login from unix shell use h only if needed. mysql dirbinmysql h hostname u root p. Create a database on the sql server. List all databases on the sql server. Switch to a database. To see all the tables in the db. To see databases field formats. To delete a db. mysql drop database database name To delete a table. Show all data in a table. SELECT FROM table name Returns the columns and column information pertaining to the designated table. Show certain selected rows with the value whatever. SELECT FROM table name WHERE field name whatever Show all records containing the name Bob AND the phone number 3. SELECT FROM table name WHERE name Bob AND phonenumber 3. Show all records not containing the name Bob AND the phone number 3. SELECT FROM table name WHERE name Bob AND phonenumber 3. Show all records starting with the letters bob AND the phone number 3. SELECT FROM table name WHERE name like Bob AND phonenumber 3. Show all records starting with the letters bob AND the phone number 3. SELECT FROM table name WHERE name like Bob AND phonenumber 3. Use a regular expression to find records. Use REGEXP BINARY to force case sensitivity. This finds any record beginning with a. SELECT FROM table name WHERE rec RLIKE a Show unique records. SELECT DISTINCT column name FROM table name Show selected records sorted in an ascending asc or descending desc. SELECT col. 1,col. FROM table name ORDER BY col. DESC Return number of rows. SELECT COUNT FROM table name Sum column. SELECT SUM FROM table name Join tables on common columns. Creating a new user. Login as root. Switch to the My. SQL db. Make the user. Update privs. mysql u root pmysql use mysql mysql INSERT INTO user Host,User,Password VALUES,username,PASSWORDpassword mysql flush privileges. Change a users password from unix shell. mysql dirbinmysqladmin u username h hostname. Change a users password from My. SQL prompt. Login as root. Set the password. Update privs. mysql u root pmysql SET PASSWORD FOR userhostname PASSWORDpasswordhere mysql flush privileges. Recover a My. SQL root password. Stop the My. SQL server process. Start again with no grant tables. Login to My. SQL as root. Set new password. Exit My. SQL and restart My. SQL server. etcinit. PASSWORDnewrootpassword where Userroot mysql flush privileges mysql quit etcinit. Set a root password if there is on root password. Update a root password. Allow the user bob to connect to the server from localhost using the password passwd. Login as root. Switch to the My. SQL db. Give privs. Update privs. mysql u root pmysql use mysql mysql grant usage on to boblocalhost identified by passwd mysql flush privileges. Give user privilages for a db. Login as root. Switch to the My. SQL db. Grant privs. Update privs. mysql u root pmysql use mysql mysql INSERT INTO db Host,Db,User,Selectpriv,Insertpriv,Updatepriv,Deletepriv,Createpriv,Droppriv VALUES. Y,Y,Y,Y,Y,N mysql flush privileges. To update info already in a table. UPDATE table name SET Selectpriv Y,Insertpriv Y,Updatepriv Y where field name user Delete a rows from a table. DELETE from table name where field name whatever Update database permissionsprivilages. Delete a column. mysql alter table table name drop column column name Add a new column to db. Change column name. Make a unique column so you get no dupes. Make a column bigger. VARCHAR3 Delete unique from table. Load a CSV file into a table. LOAD DATA INFILE tmpfilename. INTO TABLE table name FIELDS TERMINATED BY, LINES TERMINATED BY n field. Dump all databases for backup. Backup file is sql commands to recreate all dbs. mysql dirbinmysqldump u root ppassword opt tmpalldatabases. Dump one database for backup. mysql dirbinmysqldump u username ppassword databases databasename tmpdatabasename. Dump a table from a database. mysql dirbinmysqldump c u username ppassword databasename tablename tmpdatabasename. Restore database or database table from backup. mysql dirbinmysql u username ppassword databasename lt tmpdatabasename. Create Table Example 1. CREATE TABLE table name firstname VARCHAR2. VARCHAR3, lastname VARCHAR3. VARCHAR3,officeid VARCHAR1. VARCHAR1. 5,username VARCHAR8,email VARCHAR3. VARCHAR2. 5, groups VARCHAR1. DATE,timestamp time,pgpemail VARCHAR2. Create Table Example 2. MYSQL Statements and clauses. CHANGE MASTER TO. CREATE DATABASE. LOAD DATA FROM MASTER. LOAD DATA INFILE. LOAD INDEX INTO CACHE. LOAD TABLE. FROM MASTER. PURGE MASTER LOGS. ROLLBACK TO SAVEPOINT. SET SQLLOGBIN. SET TRANSACTION. SHOW BINLOG EVENTS. SHOW CHARACTER SET. SHOW CREATE DATABASE. SHOW CREATE TABLE. SHOW CREATE VIEW. SHOW INNODB STATUS. SHOW MASTER LOGS. SHOW MASTER STATUS. SHOW PRIVILEGES. SHOW PROCESSLIST. SHOW SLAVE HOSTS. SHOW SLAVE STATUS. SHOW TABLE STATUS. START TRANSACTION. String Functions. CHARACTERLENGTH. MATCH AGAINST. SUBSTRINGINDEX. UNCOMPRESSEDLENGTH. Date and Time Functions. CURRENTTIMESTAMP. Mathematical and Aggregate Functions. Flow Control Functions. Command Line Utilities. Perl API using functions and methods built into the Perl DBI with My. SQL. availabledrivers. Attributes for Handles. PHP API using functions built into PHP with My. SQL. mysqlaffectedrows. Reddit Related stories.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
November 2017
Categories |