Shulou(Shulou.com)06/02 Report--

This article will explain in detail how to achieve the connection between MySQL installation and idea. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

MySQL install connection to idea-edit the contents of the my.ini configuration file (Mysql above 8.0 is not required Directly install) [mysql] # set mysql client default character set default-character-set=utf8 [mysqld] # set port 3306 port = 3306 # set mysql installation directory basedir=E:\ MySQL5.7.13\ mysql-5.7.13-winx64# set mysql database data storage directory datadir=E:\ MySQL5.7.13\ mysql-5.7.13-winx64\ data# maximum number of connections allowed max_connections=200# service The character set used by the end defaults to the 8-bit encoded latin1 character set character-set-server=utf8# will use when creating a new table after the default storage engine default-storage-engine=INNODB# is installed Password-free access to mysqlskip-grant-tables-- to run cmd as an administrator Enter the command / / install mysqlmysqld-install / / after the installation is successful, the initialization data file mysqld-- initialize-insecure-- user=mysql// enters the mysql management interface mysql-u root-p// to modify the password update mysql.user set password=password ('new password') where user='root' / / mysql8 modify password alter user 'root'@'localhost' identified by' password 'Mysql to connect with idea 1. Import database driver

Click the link to download: (mysql driver)

Https://github.com/epochong/mysql-connector-java-8.0.16.git

After downloading, create a new lib directory under the idea directory, move the downloaded driver to the lib directory, right-click add as Library, and click the driver file again. If you can expand it, the driver is installed successfully.

If there is a driver problem during the connection process, you need to check whether the driver is added as a library, English version (add as library), and check the driver version (download driver needs to correspond to database, for example, mysql downloads mysql driver, sql server downloads sql server driver, check whether it is under the same package, sometimes the driver cannot be found without the same package).

two。 Connect to the database (the most basic connection method) package jdbc_excise;import java.sql.*;public class Jdbc {public static void main (String [] args) throws SQLException {try {Class.forName ("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://localhost:3306/student?useUnicode=true&characterEncoding=utf8&useSSL=false" / / Universal template: jdbc: database name: / / address: Port / actual database name? Additional parameters String username = "root"; String password = "123456"; Connection connection = DriverManager.getConnection (url,username,password); Statement statement = connection.createStatement (); / / execute sql query statement String sql = "select * from student"; ResultSet resultSet = statement.executeQuery (sql) While (resultSet.next ()) {System.out.println ("Sno=" + resultSet.getObject ("Sno"));} resultSet.close (); statement.close (); connection.close ();} catch (ClassNotFoundException e) {e.printStackTrace ();}

* * secure connection solution in Mad God tutorial * *

Jdbc:mysql://localhost:3306/student?useUnicode=true&characterEncoding=utf8&useSSL=false

If the mysql version is higher than the driver version, the secure connection needs to be set to false; and true will report an error.

Encapsulate the tool class to connect to the database

Write a configuration file

-- New profile: db.properties--driver=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/student?useUnicode=true&characterEncoding=utf8&useSSL=falseusername=rootpassword=123456

Encapsulation tool class

Package connect_jdbc.utils;import java.io.IOException;import java.io.InputStream;import java.sql.*;import java.util.Properties;public class JdbcUtils {private static String driver = null;; private static String url = null; private static String username = null; private static String password = null; static {try {/ / get the content in the configuration file by reflection InputStream in = JdbcUtils.class.getClassLoader () .getResourceAsStream ("db.properties") Properties properties=new Properties (); properties.load (in); driver = properties.getProperty ("driver"); url = properties.getProperty ("url"); username = properties.getProperty ("username"); password = properties.getProperty ("password"); / / load once driver Class.forName (driver) } catch (IOException e) {e.printStackTrace ();} catch (ClassNotFoundException e) {e.printStackTrace ();}} / / get the connection public static Connection getConnection () throws SQLException {return DriverManager.getConnection (url,username,password) } / / release connection public static void release (Connection conn, Statement st, ResultSet rs) {if (rsshipping null) {try {rs.close ();} catch (SQLException throwables) {throwables.printStackTrace () }} if (stalled null) {try {st.close ();} catch (SQLException throwables) {throwables.printStackTrace ();}} if (connected null) {try {conn.close () } catch (SQLException throwables) {throwables.printStackTrace ();}

Write test classes to execute sql statements

/ / execute the executeUpdate statement to add, delete and modify package connect_jdbc.utils;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class JdbcTest {public static void main (String [] args) throws SQLException {Connection connection = null; Statement st = null; ResultSet rs = null; try {connection = JdbcUtils.getConnection () } catch (SQLException throwables) {throwables.printStackTrace ();} st = connection.createStatement (); String sql = "insert into student (sno, sname, ssex, sclass, stel, sgroup, spassword)" + "values (1907040136,'he Ziqi', 'male', '1900144') 15735116626"); int I = st.executeUpdate (sql) / / the return value is an integer, indicating that several lines are affected by if (I > 0) {System.out.println ("inserted successfully!") ;} JdbcUtils.release (connection,st,rs);}}

Execute select statement

/ / execute the executeQuery statement to find package connect_jdbc.utils;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class JdbcSelect {public static void main (String [] args) {Connection connection = null; Statement st = null; ResultSet res = null; try {connection = JdbcUtils.getConnection (); st = connection.createStatement () String sqls = "select * from student"; res = st.executeQuery (sqls); / / the returned value is the result set while (res.next ()) / / the output {System.out.println (res.getObject ("sno") + "+ res.getObject (" sname ")) of the result set }} catch (SQLException throwables) {throwables.printStackTrace ();} JdbcUtils.release (connection,st,res);}} sql injection and its solution

Problem description: when using the statement function to perform the sql operation, when the input sql statement is: 'or'1=1' or' or'values > 0', the constant equals will occur to bypass the query statement, and the result set will be queried around the password, thus forming a security threat.

Solution.

The original statement function is changed to preparedStatement function, which avoids sql injection and makes the query more efficient.

Example:

Package connect_jdbc.utils;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class JdbcTestSe {public static void main (String [] args) {Connection connection = null; PreparedStatement statement = null; ResultSet res = null; try {connection = JdbcUtils.getConnection (); / / the difference from statement, do you want to use? Place placeholders instead of parameters, precompile String sql = "insert into student (sno, sname, ssex, sclass, stel, sgroup, spassword)" + "values"; / / manually give each parameter (? ) assign statement=connection.prepareStatement (sql); statement.setString (1, "1907040124"); statement.setString (2, "Xue Xiaojun"); statement.setString (3, "male"); statement.setString (4, "19070144"); statement.setString (5, "15735116626"); statement.setString (6, "3") Statement.setString (7, "123456"); / / execute int I = statement.executeUpdate (); if (I > 0) {System.out.println ("insert successful!") ;} catch (SQLException throwables) {throwables.printStackTrace ();} JdbcUtils.release (connection,statement,res) }} this is the end of the article on "how to connect MySQL installation and idea". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.