java连接MySQL数据库及实现基本的增查

来源：互联网发布：招远淘宝小镇编辑：程序博客网时间：2024/05/19 08:40

首先建立MySQL数据库：

/*
Navicat MySQL Data Transfer

Source Server : localhost
Source Server Version : 50634
Source Host : localhost:3306
Source Database : assetmanager

Target Server Type : MYSQL
Target Server Version : 50634
File Encoding : 65001

Date: 2017-08-23 09:18:41
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`userId` int(11) NOT NULL AUTO_INCREMENT,
`userName` varchar(50) DEFAULT NULL,
`userPassword` varchar(50) DEFAULT NULL,
`realName` varchar(50) DEFAULT NULL,
`sex` varchar(2) DEFAULT NULL,
`tel` varchar(50) DEFAULT NULL,
`idCard` varchar(18) DEFAULT NULL,
`address` varchar(50) DEFAULT NULL,
PRIMARY KEY (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

接着在Eclipse或者MyEclipse中导入jdbc连接包

接下来上java连接数据库代码

import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.util.Scanner;public class Testsql {String userName;String usesrPassword;public String getUserName() {return userName;}public void setUserName(String userName) {this.userName = userName;}public String getUsesrPassword() {return usesrPassword;}public void setUsesrPassword(String usesrPassword) {this.usesrPassword = usesrPassword;}public static void main(String[] args) throws Exception {try {// 加载驱动Class.forName("com.mysql.jdbc.Driver");// 创建连接Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/assetmanager", "root", "root");// 写sql语句,此处为动态添加，未赋值String sql = "insert into user (userName,userPassword) values(?,?)";// 写sqlyuju,此处为静态天剑String sql1 = "insert into user (userName,userPassword) values('fhjrkf','12345')";// 预编译的PrepareStatementPreparedStatement pr = conn.prepareStatement(sql);// Statementjava.sql.Statement st = conn.createStatement();Scanner sc = new Scanner(System.in);String userName = sc.next();String userPassword = sc.next();pr.setString(1, userName);pr.setString(2, userPassword);// 注意执行数据库插入、删除、修改(增删改)是用excuteUpdate();// 执行查询语句时使用excuteQuery();int rs = pr.executeUpdate();int rs1 = st.executeUpdate(sql1);if (rs > 0) {System.out.println("preparestatement插入成功");} else {System.out.println("preparestatement失败");}if (rs1 > 0) {System.out.println("stamtement插入成功");} else {System.out.println("statement失败");}// 使用Result获得结果集，如果有则输出到控制台。//数据库查询，执行查询语句时使用excuteQuery();String sql3 ="select * from user";PreparedStatement pr1 = conn.prepareStatement(sql3);ResultSet result = pr1.executeQuery();//打印结果集，根据主键查询，结果只能是1条记录，可以根据字段序号和字段名查询。if (result.next()) {System.out.println(result.getString("userName")+result.getString(3));}} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}

部分代码截图：

运行结果：

数据库结果：

阅读全文

0 0