在java中使用mysql

在java中使用mysql

概略

安装环境:windows 10, eclipse

安装mysql
运行mysql并且新建数据库feedback
将所需的jar添加到工程里
在代码中连接数据库并且进行查询

安装mysql

下载地址:mysql-install-community下载

下载后

安装sqlserver就行了,其他的应该暂时还不需要用到,最后需要设置密码,要记住,在后面我们需要使用账号密码登陆数据库.

运行mysql,新建数据库

登陆数据库

进入mysql的安装目录下,进入bin目录,默认应该为C:\Program Files\MySQL\MySQL Server 5.6\bin,输入
mysql -u root -p
输入你的密码

此时应该显示一大串数据库相关的内容,这个时候我们就可以输入sql语句了.
接下来我们新建数据库以便等下在java程序中使用

新建数据库并切换至新建的数据库

1. create database feedback;
2. use feedback;
3. CREATE USER sqluser IDENTIFIED BY 'sqluserpw';
4. grant usage on *.* to sqluser@localhost identified by 'sqluserpw';
5. grant all privileges on feedback.* to sqluser@localhost;
6. CREATE TABLE comments (id INT NOT NULL AUTO_INCREMENT,
7. MYUSER VARCHAR(30) NOT NULL,
8. EMAIL VARCHAR(30),
9. WEBPAGE VARCHAR(100) NOT NULL,
10. DATUM DATE NOT NULL,
11. SUMMARY VARCHAR(40) NOT NULL,
12. COMMENTS VARCHAR(400) NOT NULL,
13. PRIMARY KEY (ID));
15. INSERT INTO comments values (default, 'lars', 'myemail@gmail.com','http://www.vogella.com', '2009-09-14 10:33:11', 'Summary','My first comment');

这样,我们就新建了一个名称为feedba的数据库,并且新建了一个表table,其行分别为id,myuser,email,webpage,datum,summary,comments,primary.
我们还需要最后一步操作,输入

status

找到TCPport这一行,记住端口号.

将所需要的jar包添加到项目中

需要的jar包下载:mysql-connector-java下载
怎么导入到eclipse就请自行解决啦.

在代码中连接数据库并且进行查询

1. private Connection connect = null;
2. Class.forName("com.mysql.jdbc.Driver");
3. // Setup the connection with the DB
4. //3306是端口号(TCP port),feedback为使用的数据库(上面已经新建了),password需要替换
5. connect = DriverManager
6. .getConnection("jdbc:mysql://localhost:3306/feedback?"
7. + "user=root&password=your_passworld");
8. statement = connect.createStatement();
9. // Result set get the result of the SQL query

连接数据库后就可以执行一些相关的sql语句了,比如使用statement进行查询操作:

1. Statement statement = connect.createStatement();
2. ResultSet resultSet = statement
3. .executeQuery("select * from feedback.comments");

比如使用PreparedStatement进行插入操作

1. PreparedStatement preparedStatement = connect
2. .prepareStatement("insert into feedback.comments values (default, ?, ?, ?, ? , ?, ?)");
3. // "myuser, webpage, datum, summery, COMMENTS from feedback.comments");
4. // Parameters start with 1
5. preparedStatement.setString(1, "Test");
6. preparedStatement.setString(2, "TestEmail");
7. preparedStatement.setString(3, "TestWebpage");
8. preparedStatement.setDate(4, new java.sql.Date(2009, 12, 11));
9. preparedStatement.setString(5, "TestSummary");
10. preparedStatement.setString(6, "TestComment");
11. preparedStatement.executeUpdate();

使用PreparedStatement进行查询操作

1. preparedStatement = connect
2. .prepareStatement("SELECT myuser, webpage, datum, summary, COMMENTS from feedback.comments");
3. resultSet = preparedStatement.executeQuery();

ResultSet是一个类似迭代器的东西,可以对查询结果进行遍历

1. while (resultSet.next()) {
2. // It is possible to get the columns via name
3. // also possible to get the columns via the column number
4. // which starts at 1
5. // e.g. resultSet.getSTring(2);
6. String user = resultSet.getString("myuser");
7. String website = resultSet.getString("webpage");
8. String summery = resultSet.getString("summary");
9. Date date = resultSet.getDate("datum");
10. String comment = resultSet.getString("comments");
11. System.out.println("User: " + user);
12. System.out.println("Website: " + website);
13. System.out.println("Summery: " + summery);
14. System.out.println("Date: " + date);
15. System.out.println("Comment: " + comment);
16. }

参考资料

http://www.vogella.com/tutorials/MySQLJava/article.html
http://www.vogella.com/tutorials/MySQL/article.html

源码下载

CSDN

来自为知笔记(Wiz)

转载于:https://www.cnblogs.com/wilbertwu/p/4840594.html