java中使用mysql

来源：互联网发布：高清网络视频监控编辑：程序博客网时间：2024/05/09 17:50

在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程序中使用

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

create database feedback;use feedback; CREATE USER sqluser IDENTIFIED BY 'sqluserpw'; grant usage on *.* to sqluser@localhost identified by 'sqluserpw'; grant all privileges on feedback.* to sqluser@localhost; CREATE TABLE comments (id INT NOT NULL AUTO_INCREMENT, MYUSER VARCHAR(30) NOT NULL,EMAIL VARCHAR(30), WEBPAGE VARCHAR(100) NOT NULL, DATUM DATE NOT NULL, SUMMARY VARCHAR(40) NOT NULL,COMMENTS VARCHAR(400) NOT NULL,PRIMARY KEY (ID));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就请自行解决啦.

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

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

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

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

比如使用PreparedStatement进行插入操作

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

使用PreparedStatement进行查询操作

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

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

    while (resultSet.next()) {  // It is possible to get the columns via name  // also possible to get the columns via the column number  // which starts at 1  // e.g. resultSet.getSTring(2);  String user = resultSet.getString("myuser");  String website = resultSet.getString("webpage");  String summery = resultSet.getString("summary");  Date date = resultSet.getDate("datum");  String comment = resultSet.getString("comments");  System.out.println("User: " + user);  System.out.println("Website: " + website);  System.out.println("Summery: " + summery);  System.out.println("Date: " + date);  System.out.println("Comment: " + comment);}

参考资料

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

源码下载

CSDN

0 0