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

It is believed that many inexperienced people are at a loss about how to achieve single insert and batch insert in java connection mysql database. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

1. Connect to the database

Package com.njupt.ymh; import java.sql.DriverManager;import java.sql.SQLException;import com.mysql.jdbc.Connection; public class Connect_MySQL {private static final String URL= "jdbc:mysql://127.0.0.1:3306/news"; / / the default is 3306, which is set to 6666 (33060) MYSQL8 WMPNetworkSvc private static final String USER= "root"; private static final String PASSWORD= "12345"; private static Connection connection=null Static {/ / 1, load the driver (reflection method) try {Class.forName ("com.mysql.jdbc.Driver");} catch (ClassNotFoundException e) {e.printStackTrace ();} / 2, connect to the database try {connection= (Connection) DriverManager. GetConnection (URL, USER,PASSWORD); / / address, user name, password} catch (SQLException e) {e.printStackTrace ();}} public static Connection getConnection () {return connection;}}

2. Single insert

Package com.njupt.ymh;/** * single insert data * / import java.sql.SQLException;import java.util.List; import com.mysql.jdbc.Connection; public class OperationPaper {private static Connection connection=Connect_MySQL.getConnection (); public void addNewsPaper (NewsPaper newsPaper) {/ / add / / connection=Connect_MySQL.getConnection (); String sql= "insert into papertest (id, date, title, lead_pargraph, full_text) values (?,?)"; java.sql.PreparedStatement ptmt = null Try {ptmt = connection.prepareStatement (sql);} catch (SQLException E1) {e1.printStackTrace ();} try {ptmt.setLong (1, newsPaper.getID ()); ptmt.setString (2, newsPaper.getDate ()); ptmt.setString (3, newsPaper.getTitle ()); ptmt.setString (4, newsPaper.getLead ()); ptmt.setString (5, newsPaper.getfull ()); ptmt.execute () / / execute the given SQL statement, which may return multiple results} catch (SQLException e) {e.printStackTrace ();}} public static void main (String [] args) {OperationPaper operationPaper = new OperationPaper (); List listFile = SearchFile.getAllFile ("E:\ huadai\\ 1996\ 7\ 21", false); / / File list for (String string: listFile) {NewsPaper newsPaper = new NewsPaper (string); if (newsPaper.isUseful ()) operationPaper.addNewsPaper (newsPaper) / / insert database}

3. Batch insert

Package com.njupt.ymh; import java.sql.SQLException;import java.util.ArrayList;import java.util.List; import com.mysql.jdbc.Connection; public class OperaOnNewsPaper implements Cloneable {private static Connection connection=Connect_MySQL.getConnection (); / * supports bulk data insertion * @ param newsPaper * / public void addNewsPaper (ArrayList listNewsPaper) {/ / add String sql= "insert into papertest (id, date, title, lead_pargraph, full_text) values (?,?)" Java.sql.PreparedStatement ptmt = null; try {connection.setAutoCommit (false); / / close transaction ptmt = connection.prepareStatement (sql);} catch (SQLException e2) {e2.printStackTrace ();} for (NewsPaper paperaper: listNewsPaper) {try {ptmt.setLong (1, paperaper.getID ()); ptmt.setString (2, paperaper.getDate ()); ptmt.setString (3, paperaper.getTitle ()); ptmt.setString (4, paperaper.getLead ()); ptmt.setString (5, paperaper.getfull ()) Ptmt.addBatch ();} catch (SQLException e) {e.printStackTrace ();}} try {ptmt.executeBatch (); / / execute a given SQL statement, which may return multiple results connection.commit ();} catch (SQLException e) {e.printStackTrace ();}} public static void main (String [] args) {OperaOnNewsPaper operation = new OperaOnNewsPaper (); List listFile = SearchFile.getAllFile ("E:\\ huadai\\ 2007", false) / / File list ArrayList listPaper = new ArrayList (); int count = 0; int sizenum = 1000; for (String string: listFile) {NewsPaper newsPaper = new NewsPaper (string); if (newsPaper.isUseful ()) {count++; listPaper.add (newsPaper); / / News list if (count% sizenum = = 0) {/ / System.out.println ("ok"); System.out.println ("" + count); operation.addNewsPaper (listPaper); / / insert database System.out.println (count) ListPaper.clear ();} if (count% sizenum! = 0) {operation.addNewsPaper (listPaper); System.out.println ("zui hou");}}}

Through the actual test, about 100,000-level data batch insertion or a single insert to save about 10 minutes. Because every time a single entry is inserted, a connection is established with the database and a log update is made. However, if one of the batch data values does not conform to the format in the process of batch insertion, it will lead to the overall failure of the batch insertion, so it is necessary to deal with the failure or preprocess the data inserted in batches to ensure the success of the batch insertion.

After reading the above, have you mastered how to connect java to mysql database to achieve single insert and batch insert? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.